# Thread: Sum if meets two criteria (2002)

1. ## 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 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!

2. ## 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.

3. ## 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

4. ## 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

5. ## 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

6. ## 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

7. ## Re: Sum if meets two criteria (2002)

<P ID="edit" class=small>(Edited by sdckapr on 20-Jan-05 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

8. ## Re: Sum if meets two criteria (2002)

And thank you, Steve. That helped.

Sarah

9. ## 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•