SumIF on varying columns for months

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
199
I have a spreadsheet with differing prices in Column K. I have 12 Columns (AQ through BB) representing each of the months. A "1" in the appropriate month column tells me that that row relates to the month of the Column -- thus AR is for the month of February, etc.

I want to sum those prices in Column K for those rows which pertain to the current month.

This formula works: =SUMIF(AR10:AR838,"=1",K10:K838) and gives me what I want.

However, I need to remember each month to change the formula, so that, when March arrives I'll need to change the forumla to =SUMIF(AS10:AS838,"=1",K10:K838) -- since AS is the column for March.

I'd like to have a forumla that would adjust automatically according to the current month.

In cell T1 I have =Today() and I can do a =Month(T1) which gives me a 2 as the answer, which, of course, relates to the second month, thus february.

I'm struggling to find a way to use the current month to trigger which Column should be used in the =SUMIF(AR10:AR838,"=1",K10:K838) formula.

In words what I want is a way to say, "use the current month to select the appropriate month column for the SUMIF function.

Thanks for any help
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
I have a spreadsheet with differing prices in Column K. I have 12 Columns (AQ through BB) representing each of the months. A "1" in the appropriate month column tells me that that row relates to the month of the Column -- thus AR is for the month of February, etc.

I want to sum those prices in Column K for those rows which pertain to the current month.

This formula works: =SUMIF(AR10:AR838,"=1",K10:K838) and gives me what I want.

However, I need to remember each month to change the formula, so that, when March arrives I'll need to change the forumla to =SUMIF(AS10:AS838,"=1",K10:K838) -- since AS is the column for March.

I'd like to have a forumla that would adjust automatically according to the current month.

In cell T1 I have =Today() and I can do a =Month(T1) which gives me a 2 as the answer, which, of course, relates to the second month, thus february.

I'm struggling to find a way to use the current month to trigger which Column should be used in the =SUMIF(AR10:AR838,"=1",K10:K838) formula.

In words what I want is a way to say, "use the current month to select the appropriate month column for the SUMIF function.

Thanks for any help
Sheet1

*ABCKLMTAQARASATAUAVAWAXAYAZBABB
1******01-Mar************
2*******************
3*******************
4*******************
5*******************
6*******************
7*******************
8*******************
9*******JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
10*56*30****11*********
11***22*****2*********
12***26*****1*********
13*******************
14*******************
15*******************

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:53px;"><col style="width:64px;"><col style="width:1px;"><col style="width:1px;"><col style="width:63px;"><col style="width:73px;"><col style="width:65px;"><col style="width:65px;"><col style="width:65px;"><col style="width:65px;"><col style="width:65px;"><col style="width:65px;"><col style="width:65px;"><col style="width:76px;"><col style="width:65px;"><col style="width:73px;"><col style="width:71px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B10{=SUMIF(INDEX(AQ10:BB838,0,MATCH(MONTH(T1),MONTH($AQ$9:$BB$9),0)),"=1",K10:K838)}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Not sure where you headers are but if in fifferent row just change it.
 
Upvote 0
Thanks -- this works wonderfully -- Now I'll have to analyze the forumla so that I can understand it.

Best wishes,

I will also check out the Array formula.
You're welcome, thanks for the feedback.

Its just a normal SUMIF but the first argument picks out the appropriate column.

Where the row_num parameter is set to zero in the INDEX function, it returns the entire column specified by the column_num parameter (i.e. the month in your case).

A couple of excellent articles for a better understanding of the INDEX and SUMIF functions are:
 
Upvote 0
You're welcome, thanks for the feedback.

Its just a normal SUMIF but the first argument picks out the appropriate column.

Where the row_num parameter is set to zero in the INDEX function, it returns the entire column specified by the column_num parameter (i.e. the month in your case).

A couple of excellent articles for a better understanding of the INDEX and SUMIF functions are:


Thanks again
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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
Back
Top