Results 1 to 9 of 9
  1. #1
    Star Lounger
    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 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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

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

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

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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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 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. #8
    Star Lounger
    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

  9. #9
    3 Star Lounger
    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

Posting Permissions

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