# Thread: SumIF on varying columns for months Thanks: 0 Likes: 0

1. ## SumIF on varying columns for months

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

2. ## Re: SumIF on varying columns for months

Originally Posted by StanSz
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

 * A B C K L M T AQ AR AS AT AU AV AW AX AY AZ BA BB 1 * * * * * * 01-Mar * * * * * * * * * * * * 2 * * * * * * * * * * * * * * * * * * * 3 * * * * * * * * * * * * * * * * * * * 4 * * * * * * * * * * * * * * * * * * * 5 * * * * * * * * * * * * * * * * * * * 6 * * * * * * * * * * * * * * * * * * * 7 * * * * * * * * * * * * * * * * * * * 8 * * * * * * * * * * * * * * * * * * * 9 * * * * * * * January February March April May June July August September October November December 10 * 56 * 30 * * * * 1 1 * * * * * * * * * 11 * * * 22 * * * * * 2 * * * * * * * * * 12 * * * 26 * * * * * 1 * * * * * * * * * 13 * * * * * * * * * * * * * * * * * * * 14 * * * * * * * * * * * * * * * * * * * 15 * * * * * * * * * * * * * * * * * * *

 Cell Formula B10 {=SUMIF(INDEX(AQ10:BB838,0,MATCH(MONTH(T1),MONTH(\$AQ\$9:\$BB\$9),0)),"=1",K10:K838)}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

Not sure where you headers are but if in fifferent row just change it.

3. ## Re: SumIF on varying columns for months

Hi,

Maybe:

Code:
```=SUMIF(
INDEX(AQ10:BB838, 0, MONTH(T1)),
1,
K10:K838)```

4. ## Re: SumIF on varying columns for months

Originally Posted by circledchicken
Hi,

Maybe:

Code:
```=SUMIF(
INDEX(AQ10:BB838, 0, MONTH(T1)),
1,
K10:K838)```
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.

5. ## Re: SumIF on varying columns for months

Originally Posted by StanSz
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:

6. ## Re: SumIF on varying columns for months

Originally Posted by circledchicken
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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•