# Thread: SumIf with multiple criteria (XP)

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