SumIf Query

Coventry Deb

New Member
Joined
Oct 19, 2005
Messages
29
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!
 
Upvote 0
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 :biggrin:
 
Upvote 0
Domenic,

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

regards,
shodan
 
Upvote 0
ADVERTISEMENT
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.
 
Upvote 0
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!
 
Upvote 0
ok thanks for the info, thats clear, tried something myself, but did some stupid things if I look to it afterwards.
 
Upvote 0

Forum statistics

Threads
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?

Disable AdBlock

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
Back
Top