Results 1 to 9 of 9
Thread: Sum if meets two criteria (2002)

20050119, 21:57 #1
 Join Date
 Apr 2002
 Location
 Pleasant Hill, CA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sum if meets two criteria (2002)
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 fastapproaching! 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!

20050119, 22:37 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Sum if meets two criteria (2002)
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.

20050119, 22:46 #3
 Join Date
 Jul 2001
 Location
 Long Beach, California, USA
 Posts
 233
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum if meets two criteria (2002)
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

20050120, 14:58 #4
 Join Date
 Apr 2002
 Location
 Pleasant Hill, CA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum if meets two criteria (2002)
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

20050120, 15:06 #5
 Join Date
 Apr 2002
 Location
 Pleasant Hill, CA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum if meets two criteria (2002)
Hans, please see comments above (I wasn't sure if "Reply to Post" would send both of you an update).Sarah

20050120, 15:16 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sum if meets two criteria (2002)
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$2 1)
=SUMPRODUCT(($A$2:$A$21="A")*(ISNUMBER(SEARCH("CCS ",$B$2:$B$21)))*ISNUMBER($C$2:$C$21),$C$2:$C$2 1)
Steve

20050120, 15:20 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sum if meets two criteria (2002)
<P ID="edit" class=small>(Edited by sdckapr on 20Jan05 11:20. Modified explanation, not blanks, but text issue)</P>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")*IS NUMBER($C$2:$C$21),$C$2:$C$21)
=SUMPRODUCT(($A$2:$A$21="A")*($B$2:$B$21="CCS")*IS NUMBER($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

20050120, 15:39 #8
 Join Date
 Apr 2002
 Location
 Pleasant Hill, CA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum if meets two criteria (2002)
And thank you, Steve. That helped.
Sarah

20050123, 11:08 #9
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum if meets two criteria (2002)
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.Microsoft MVP  Excel