Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SumIf with multiple criteria (XP)

    Hello everybody!

    I need to do the following: A datasheet contains two columns with data. The value of the first column has to fulfill certain criteria. If fulfilled, the values of the second column has to be added. This could be done with the SumIf-function - at least if you have to compare to a constant value. But how could it work if you have to compare to two criteria? Or if your criteria is a variable?
    To make the problem more understandable, I have attached a sheet, which contains the problem.
    I am really curious about a solution.

    Best regards, Porley

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SumIf with multiple criteria (XP)

    Hi Porley

    You could use 2 SUMIF functions to work out the total >=0 and subtract those >70

    <big><code>=SUMIF($A$2:$A$141,">=0",$B$2:$B$141)-SUMIF($A$2:$A$141,">70",$B$2:$B$141)</code></big>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SumIf with multiple criteria (XP)

    One possibility is to subtract the SUMIF for negative values from the SUMIF for less than or equal to 70:

    =SUMIF(A2:A141,"<=70",B2:B141)-SUMIF(A2:A141,"<0",B2:B141)

    Another one is to use SUMPRODUCT:

    =SUMPRODUCT((A2:A141>=0)*(A2:A141<=70)*(B2:B141))

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf with multiple criteria (XP)

    Hello Tony, hello Hans

    thanks for your solutions. I found the SUMPRODUCT-solution fits my needs best, since it allows to make the criteria parametrically. So thanks again!!

    Best regards, Porley

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SumIf with multiple criteria (XP)

    The other solution can use variable limits too. If the lower limit (e.g. 0) is in D1 and the upper limit (e.g. 70) in D2, you can use this variation on Tony's formula:

    =SUMIF($A$2:$A$141,">="&D1,$B$2:$B$141)-SUMIF($A$2:$A$141,">"&D2,$B$2:$B$141)

  6. #6
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf with multiple criteria (XP)

    Thanks again.

    It is good to know that one may mix the criteria like this.

    Best regards, Porley

Posting Permissions

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