Calc: Filtering formula

users
#1

Hi,

I have two adjacent columns where the first contains text and the second contains 9 digit numbers both above and below zero.

I would like to SUM the contents of the second column provided it meets two criteria, namely that the first column contains a certain text string and the number corresponding to that text string in the second column is greater than zero.

I have tried SUMIFS, AND with SUMIF and Vlookup but cannot seem to get the formula correct i.e. the error seems to always be that there is a missing operator in the string yet checking back I see all commas are in the right places. I am placing the text to search for in quotation marks as well as the "0<" or "0>"

Any help appreciated

Regards
Hylton

#2

Assuming labels are in A1:A30, values in B1:B30
=SUMIFS(B1:B30,A1:A30,"=<text>",B1:B30,">0")

replace <text> with the 'certain text string'

----- unless you are looking for a text string within the contents of
the cells with text. That would be more complicated. I'd go for a (if
necessary, hidden) column:

Assuming original labels in A1:A30, values in C1:C30
cell B1 formula: =LEN(IF(ISERR(FIND("<text to look for>",A1)),"",A1))
<-- formula copied through B30
then
=SUMIFS(D1:D30,B1:B30,">0",D1:D30,">0")

Let me know if that helps.

#3

I messed up. Should be:

Assuming labels are in A1:A30, values in B1:B30
=SUMIFS(B1:B30,A1:A30,"=<text>",B1:B30,">0")

replace <text> with the 'certain text string'

----- unless you are looking for a text string within the contents of
the cells with text. That would be more complicated. I'd go for a (if
necessary, hidden) column:

Assuming original labels in A1:A30, values in C1:C30
cell B1 formula: =LEN(IF(ISERR(FIND("<text to look for>",A1)),"",A1))
(copy this formulas through B30)

then
=SUMIFS(C1:C30,B1:B30,">0",C1:C30,">0")

#4

I messed up. Should be:

Thank you for the correction. The formula, with a little amendment works flawlessly.

=SUMIFS($D$7:$D$1000,$C$7:$C$1000,"=Sold",$D$7:$D$1000,"<0")

With this formula I can now search for a certain string in column C and SUM the corresponding values in column D.

Thank you, I will certainly be kept in my personal FAQ file.