SumIf Query

[Coventry Deb]

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

 

[Domenic]

Try...

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

Hope this helps!

 

[Coventry Deb]

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

 

[shodan]

Domenic,

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

regards,
shodan

 

[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

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.

 

[Domenic]

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!

 

[shodan]

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