Conditional sum

Posted by David Stanley on November 06, 2001 12:36 PM

I am having trouble getting a sum other than 0 when I do a conditional sum on two or more conditions. I am not getting an error, just 0. Thank you for your help in advance

Posted by Aladin Akyurek on November 06, 2001 12:42 PM

Care to post the formula you are using or the details of the problem you want to solve?

Aladin

Posted by David Stanley on November 07, 2001 4:53 AM

=SUM(IF($L$2:$L$136="CCA*",IF($N$2:$N$136="DIST*",$O$2:$O$136,0),0))
I think I need to add AND(*) some where. I want to sum column O if column L=CCA and column N=DIST
Thank you for your time.

Posted by Aladin Akyurek on November 07, 2001 5:34 AM

David,

Try this alternative:

If only DIST and CCA are of interest (not e.g., DISTRIB or CCAX, etc, use:

=SUMPRODUCT(($L$2:$L$136="CCA")*($N$2:$N$136="DIST"),($O$2:$O$136))

Otherwise use:

=SUMPRODUCT((ISNUMBER(SEARCH("CCA*",$L$2:$L$136)))*(ISNUMBER(SEARCH("DIST*",$N$2:$N$136))),($O$2:$O$136))

Aladin