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 -- To unsubscribe e-mail to: [hidden email] 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 |
On 1 December 2017 at 13:25, Hylton Conacher (ZR1HPC)
<[hidden email]> 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: [hidden email] 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 |
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") -- T. R. Valentine A rich heart may be under a poor coat. -- To unsubscribe e-mail to: [hidden email] 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 |
On 01/12/2017 21:57, T. R. Valentine wrote:
> 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. > > 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") > > > -- To unsubscribe e-mail to: [hidden email] 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 |
Free forum by Nabble | Edit this page |