Sumif On Multiple Columns Using Multiple Criteria By Passing A Range Of Values

[Luke M]

I am looking for a formula (or macro) that can return the aggregate a range of numbers for a country, having taken account of multiple criteria for multiple columns.

Please see .xlsx for example.


This seems to be a simple problem but has even eluded professional modellers at work!

Any help would be much appreciated!

[Safal Shrestha]

Sorry if I am not clear about the problem. Can you clearify one thing for me so that I can try helping you with your problem?

The criteria columns seems exhaustive with the dummy set data. Can you please explain it for me?

As far as I can think, sumproduct with '+' for multiple criteria in a criteria range is the solution for the problem.

Br,

[Luke M]

Sure,

Although the criteria is exhaustive at the moment, the idea is that I will be able to delete (or select) specific criteria and the formula would update to reflect the new totals.... eventually I intend to have these criteria as 'tick boxes' so that I can select or de-select at will and the (required) forula will update automatically.

Having tried (and failed) with the sumproduct function (it returned incorrect amounts), im thinking that an advanced filter macro followed by some kind of sum function might be the answer, but would love it the sumproduct function does have the answer! .

Thanks again

[Excel Fox]

You've included ALL possible criteria in the criteria column. Based on that, US should be 37, UK 33 and China 50. Is that correct?

[Excel Fox]

I am guessing this should suffice

=SUMPRODUCT(ISNUMBER(MATCH($C$17:$C$43,$B$7:$B$10, 0)*MATCH($D$17:$D$43,$C$7:$C$10,0)*MATCH($E$17:$E$ 43,$D$7:$D$10,0))*($F$17:$F$43)*($B$17:$B$43=F7))

[Excel Fox]

Here's the file BTW. And yes, it's a SUMPRODUCT :)

[Luke M]

That is EXACTLY what Im after, (and have been for a while!) Many Thanks! (I shall leave the modelling expert at work to stew over this a little longer as punishment!) %p

[Excel Fox]

Great. Glad you found what you were looking for. And the excel modelling expert, don't cane him ;). 'Expert' is a relative term.