# SumIf Query

#### Coventry Deb

##### New Member
I have various descriptions going down column A and Years across the rows and amounts as appropriate in the cells

Column A starting at row 225
25% Discounts
50% Disounts
Benefits
Exemptions

The formula =SUMIF(A225:A241,"=Benefits",D225:K241) does not work unless it's only one column of figures you want to sum (which as you can see by the range D225 to K241 I don't) - any ideas?

Help

Deb

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Try...

=SUMPRODUCT((A225:A241="Benefits")*(D225:K241))

Hope this helps!

Fantastic - Thank you Domenic

I assume it works on a False or True basis namely if its False multiply the range to be summed by 0, if true multiply the range to be summed by 1.

Am I correct?

Ta

Deb

Domenic,

How would you to do this when you would have multiple conditons?
eg; column b contains a retailer( ret1, ret2,..)

regards,
shodan

Coventry Deb said:
I assume it works on a False or True basis namely if its False multiply the range to be summed by 0, if true multiply the range to be summed by 1.

Am I correct?

Ta

Deb

Yes, that's right. The numerical equivalent of TRUE and FALSE is 1 and 0, respectively. Therefore, SUMPRODUCT multiplies each cell in D225:K241 by its corresponding value in Column A and sums the result.

shodan said:
Domenic,

How would you to do this when you would have multiple conditons?
eg; column b contains a retailer( ret1, ret2,..)

regards,
shodan

Using the same example, you can have the following...

=SUMPRODUCT((A225:A241="Benefits")*(B225:B241="Retailer")*(D225:K241))

Hope this helps!

ok thanks for the info, thats clear, tried something myself, but did some stupid things if I look to it afterwards.

Replies
4
Views
233
Replies
1
Views
302
Replies
0
Views
184
Replies
1
Views
716
Replies
1
Views
157

1,196,273
Messages
6,014,382
Members
441,817
Latest member
biRosETa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back