SUMIF with variable Sum_Range

[AlbertV]

I am working on a spreadsheet that calculates the monthly price based on the number of active support criteria. Normally I would use a standard SUMIF function, however the column it uses for the sum range needs to be variable based on a criteria (month).

A B C D
Jan-09 Feb-09
Server1 Basic 1 0
Server2 High 1 1
Server3 Basic 0 1

So normally I would use SUMIF(B:B,"Basic",C:C), but now I need it to have the C:C variable based on on the month.

 

[NBVC]

Something like this:

=SUMPRODUCT((B2:B4="Basic")*(TEXT(C1:D1,"mmm")="Jan"),C2:D4)

where dates are in C1:D1

Adjust ranges to suit (don't use whole column references, use defined ranges)

Also, you can replace "Jan" with a cell reference containing "Jan" without quotes, so that you can be more flexible.

 

[barry houdini]

You could use SUMIF with an INDEX function for the range to sum, e.g.

=SUMIF(B2:B100,"Basic",INDEX(C2:N100,0,MATCH(P1,C1:N1)))

where P1 contains a date in the month you want to sum, C1:N1 contains the 1st of each month

 

[rassten]

if column C is jan-09 and column D is feb-09; then it is still
SUMIF(B:B,"Basic",C:C)

 

[AlbertV]

NBVC, thanks very much, I knew it should be possible with INDEX MATCH, but was not able to get it right. Works as a dream.

Barry, I have tried the Sumproduct one as well, but could not get it to work (probably me).

Thanks for you help,

Albert