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

**Luke M** · 05-15-2013, 01:54 PM

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** · 05-15-2013, 02:43 PM

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))

**Luke M** · 05-15-2013, 02:53 PM

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!)