SUMIF with variable Sum_Range

AlbertV

New Member
Joined
Sep 15, 2009
Messages
4
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Active Member
Joined
Aug 31, 2008
Messages
310
if column C is jan-09 and column D is feb-09; then it is still
SUMIF(B:B,"Basic",C:C)
 

AlbertV

New Member
Joined
Sep 15, 2009
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top