Share via

Sumifs function required with dynamic range

Waseem Ahmad 20 Reputation points
2025-09-07T05:10:14.37+00:00

Hi Experts,

i am using below sumifs function, i just want to change it as per my requirements. every month i need to change sumifs range of column as mention ($D$6:$D$16) to ($E$6:$E$16) and so on.

is there any solution in formula when ever i change column reference like (1, 2 or D, E), it automatically change the column reference in formula.

hope you all understand my point.

Regards

Waseem

1

Microsoft 365 and Office | Excel | Other | Windows
0 comments

Answer accepted by question author

  1. Arlene D 36,035 Reputation points Independent Advisor
    2025-09-07T06:40:58.63+00:00

    Thanks for clearing that up, Waseem. So if I understand you correctly, you have only one result column, and each month you want the formula to switch automatically to the next month’s numbers without changing the formula manually.

    In that case, the easiest way is to tie the formula to a cell where you enter the month number. For example, if you put the month number in cell L1 (1 for Month-1, 2 for Month-2, etc.), you can use:

    =SUMIFS(INDEX($D$6:$J$16,0,$L$1),$C$6:$C$16,L6)

    $D$6:$J$16 is the full block of all months.

    $L$1 tells Excel which month column to use.

    $C$6:$C$16,L6 still matches the code.

    Now each time you change the number in L1, your result column updates to show that month’s sums.

    Do you want the month to change automatically with the system date, or are you fine updating the month number manually in a cell?

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Waseem Ahmad 20 Reputation points
    2025-09-07T05:58:45.4166667+00:00

    My apology, actually my result column is only one,

    every month need top populate next month sum values in it.

  2. Arlene D 36,035 Reputation points Independent Advisor
    2025-09-07T05:45:00.8533333+00:00

    Got it Waseem, I see what you mean. Right now your formula is stuck on column D, so every month you need to rewrite it. A simpler way is to make the column reference flexible.

    Try this version:

    =SUMIFS(INDEX($D$6:$J$16,0,COLUMN()-COLUMN($M$1)+1),$C$6:$C$16,$L6)

    How it works:

    $D$6:$J$16 covers all months in one block.

    COLUMN()-COLUMN($M$1)+1 figures out which month to pick, based on where the formula sits in the result table.

    $C$6:$C$16,$L6 keeps matching the code.

    Now when you drag the formula across Month-1 to Month-7, Excel will pull the right column automatically. No need to edit the formula each time.

    Hope that helps!

    Regards,

    Arlene D.

    0 comments
  3. riny 20,870 Reputation points Volunteer Moderator
    2025-09-07T05:44:47.94+00:00

    Rather then using $D$6:$D$16, use D$6:D$16

    When you copy this down it will remain but when you copy it to the right D will become E, then F etc.

    Does that fix it for you?

    Edit:

    Just to give you the complete picture, the formula in M6 should be:

    =SUMIFS(D$6:D$16,$C$6:$C$16,$L6)

    Copy down and to the right.

    User's image

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.