Variable sumif?

[BRISBANEBOB]

How can I =sumif when I have variable criteria? Or should I be using =sumproduct? The attached sample file explains it better.

Thanks for all ideas.

[DonkeyOte]

SUMIF will suffice - use INDEX (with MATCH) to establish the variable sum_range

=SUMIF($B$3:$B$11,$B$16,INDEX($C$3:$N$11,0,MATCH($B17,$C$2:$N$2,0)))

[BRISBANEBOB]

Please can you explain what function the '0' plays in the Index formula?

[DonkeyOte]

all rows - see XL help for more info.

[TMS]

Try:

Please Login or Register  to view this content.

Regards

[DonkeyOte]

@TMShucks - the below:

Please Login or Register  to view this content.

is Volatile given use of OFFSET but also [interestingly?] (XL2002+) by the fact that the optional sum_range is smaller than the non-optional range
(see: http://www.decisionmodels.com/calcsecretsi.htm - this behaviour was actually discovered on this board).

The INDEX alternative is semi-volatile only - will calculate upon workbook open but thereafter only on those occasions where a direct precedent is altered.

[TMS]

@DonkeyOte.

Thanks for the guidance. Are you saying that the formula *will* work but should not be used ... or rather, that the alternative you provided is a better option?

If it works, is the impact on calculation, etc., significant. Is it acceptable if used sparingly?

Thanks ... sorry to be pedantic but I'd like to understand a little more.

Regards

[DonkeyOte]

I personally prefer non-volatile alternatives but OFFSET is very fast so on a one-off basis OFFSET is probably quicker.

However, goes without saying that the impact Volatility will have on a model depends on the specifics - ie number of Volatiles and number of Volatile actions being performed (we assume Auto Calc.)

Regards that specific construct - I would say it's generally accepted practice that you should endeavour to make the dimensions of both ranges consistent.

=SUMIF(B3:B11,B16,OFFSET(B3:B11,0,MATCH(B17,C2:N2,0)))

would be preferable

Point being that if we disregard OFFSET (ie remove that Volatile element) and have a construct say of:

=SUMIF(B3:B11,B16,C3)

the above is open to error prior to XL2002 and is Volatile thereafter (the sum_range is implicit as far as XL is concerned)

[TMS]

@DonkeyOte

Thank you for the clarification ... and for spending the time to explain so fully.

Regards