Re: [libreoffice-users] Calc: Filtering formula

On 1 December 2017 at 13:25, Hylton Conacher (ZR1HPC)
<hylton@conacher.co.za> wrote:

> 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>"

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.

-- 
T. R. Valentine
A rich heart may be under a poor coat.

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted