# Changing SUM_Range in a SUMIFS or another formula

1. ## Changing SUM_Range in a SUMIFS or another formula

I have a formula in a cell as below

In cell A1, i want to have a drop-down list from JAN to DEC (all twelve months). Based on the month i select, i want the row "N:N" to change to a different value.
If A1 is Jan, then in SUMIFS should have N:N as range, if it is Feb, then the next column (O:O), for MAR it should be P:P...so on for all twelve months.

How to do this? also, can the solution you provide be used in other formulas as well? there are several other formula that use range N:N & i want all of them to change when the month is changed.

2. ## Re: Changing SUM_Range in a SUMIFS or another formula

Do the N, O,... columns have a header?

Try this:

4. ## Re: Changing SUM_Range in a SUMIFS or another formula

Originally Posted by Pepe Le Mokko
Do the N, O,... columns have a header?
No. There is no header. Also, the range is not the whole column (only from N24 to N999). Thanks.

5. ## Re: Changing SUM_Range in a SUMIFS or another formula

Originally Posted by Phuocam
Try this:

Hello, As i mentioned below, the range is not the whole column. So, when I put SUMIFS(INDEX(\$N\$24:\$N\$999:\$Y\$24:\$Y\$999,,MONTH(1&A1)),D:D,AE19,K:K,AD\$19), it is not working i get "VALUE!" error.
when i go through the steps pf the formula, it seems correctly take the rage but the output is an error!

6. ## Re: Changing SUM_Range in a SUMIFS or another formula

Originally Posted by mohanys
Hello, As i mentioned below, the range is not the whole column. So, when I put SUMIFS(INDEX(\$N\$24:\$N\$999:\$Y\$24:\$Y\$999,,MONTH(1&A1)),D:D,AE19,K:K,AD\$19), it is not working i get "VALUE!" error.
when i go through the steps pf the formula, it seems correctly take the rage but the output is an error!
Sorry, got it! i have to change the D & K columns also to \$D\$24:\$D\$999 & \$K\$24:\$K\$999 respectively. Thank you for the help.

7. ## Re: Changing SUM_Range in a SUMIFS or another formula

Change:

Originally Posted by mohanys
SUMIFS(INDEX(\$N\$24:\$N\$999:\$Y\$24:\$Y\$999,...
to:

There are currently 1 users browsing this thread. (0 members and 1 guests)