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!
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,
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
Last edited by Excel Fox; 05-15-2013 at 02:55 PM. Reason: Quote Removed
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?
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
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))
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Here's the file BTW. And yes, it's a SUMPRODUCT
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
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!)
Great. Glad you found what you were looking for. And the excel modelling expert, don't cane him. 'Expert' is a relative term.
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Posting Permissions
Reply With Quote
Bookmarks