Sum if meets two criteria (2002)

Topic

New Reply

Hello…I have been searching the posts to find a similar question and seem to be getting close to finding an answer. But my customer’s deadline is fast-approaching! I have a worksheet with 3 columns: Project, Department, Capital. I would like to find the total Capital needed for all Project A’s that are in Department C&C. Basically, I scan through column A and check the value for Project “A.” Then with those results I scan their column B for Department “C&C.” With those results I add their column C value for Capital.

I need to know how to check column A’s range and then use something like SUMIF(B2:B9,”C&C”,C2:C9). Pivot tables seems to perform a count only, instead of adding the capital. Thanks in advance!

Reply | Quote

Replies

Use SUMPRODUCT:

=SUMPRODUCT(($A$2:$A$9=”A”)*($B$2:$B$9=”C&C”)*$C$2:$C$9)

A pivot table works too. See attached.

Reply | Quote

Hans, please see comments above (I wasn’t sure if “Reply to Post” would send both of you an update).–Sarah

Reply | Quote

SarahCarter,

I used an array formula to return the results you want. {=SUM(($A$2:$A$9=”A”)*($B$2:$B$9=”C&C”)*$C$2:$C$9)}
I have attached your workbook with these included.

Brent

Reply | Quote

Thank you, Hans and Brent. I tried Hans’ solution first and it worked beautifully! I did encounter a snag when my data in column B Department had a combination of the search words. I will attach a revised spreadsheet for your review. Perhaps you can advise on how to adjust the formula? The calculation works when the ranges are through row 13, but once past that the #VALUE result appears. I tried to evaluate the formula but can’t figure out where the error occurs. Row 13 is the start of the combined criteria “CCS, C&C.” So it’s checking that row but then stops. Thanks again!

Sarah

Reply | Quote

For “contains”, you can use SEARCH (use FIND if you want case sensitive)

=SUMPRODUCT(($A$2:$A$21=”A”)*(ISNUMBER(SEARCH(“C&C”,$B$2:$B$21)))*ISNUMBER($C$2:$C$21),$C$2:$C$21)
=SUMPRODUCT(($A$2:$A$21=”A”)*(ISNUMBER(SEARCH(“CCS”,$B$2:$B$21)))*ISNUMBER($C$2:$C$21),$C$2:$C$21)

Steve

Reply | Quote

And thank you, Steve. That helped.

Sarah

Reply | Quote

If you use the comma syntax as you do (in part, anyway), the ISNUMBER($C$2:$C$21) conditional/term can be omitted.

The full comma syntax would require something like:

=SUMPRODUCT(–($A$2:$A$21=”A”),–ISNUMBER(SEARCH(“C&C”,$B$2:$B$21)),$C$2:$C$21)

=SUMPRODUCT(($A$2:$A$21=”A”)+0,ISNUMBER(SEARCH(“C&C”,$B$2:$B$21))+0,$C$2:$C$21)

depending on the coercer (–, +0, *1, etc.) one opts for.

The comma sysntax ignores text values in the range to sum. The only time a #VALUE! error can occur is when the range to sum entirely consists of text values (for which I think the code of SumProduct must be corrected). While at it, if SumProduct is allowed to implicitly coerce the conditionals to arrays of 1’s and 0’s, the formulas with this function would operate significantly faster.

Reply | Quote

(Edited by sdckapr on 20-Jan-05 11:20. Modified explanation, not blanks, but text issue)

You have a space in C14 (it was not the blanks in COlumn C) which is not numbers and can not be added; Add another criteria:

=SUMPRODUCT(($A$2:$A$21=”A”)*($B$2:$B$21=”C&C”)*ISNUMBER($C$2:$C$21),$C$2:$C$21)
=SUMPRODUCT(($A$2:$A$21=”A”)*($B$2:$B$21=”CCS”)*ISNUMBER($C$2:$C$21),$C$2:$C$21)
=SUMPRODUCT(($A$2:$A$21=”A”)*($B$2:$B$21=”CCS, C&C”)*ISNUMBER($C$2:$C$21),$C$2:$C$21)

Steve

Reply | Quote