Excel Summing a chart Based on Month

Question

Above is a chart I am trying to sum based on the month. Ie I want a running total that I don't have to adjust as each month changes. I know it may look a little strange but the fiscal year for us goes from Feb to Jan of the next year.

ie: =SUM(C10:D10) since its only march.

So i was looking at using something like: =Text(Today(),"mmmm" that would give me the Month of March then i would be able to match it to the month that im on

then doing something like:

=SUMPRODUCT(INDEX(C10:F10,,MATCH(TEXT(TODAY(),"mmmm"),Table10[[#Headers],[February]:[May]],0)))

this give me the current month only would still need to figure out how to sum the month before etc.

or

=SUMIFS(C10:N10,Table10[[#Headers],[February]:[January]],">="&TEXT(TODAY(),"mmmm"))

This give me -43 cant figure that out

Thanks in advance

Scott Craner · Accepted Answer · 2024-03-26 18:19:58Z

2

Use INDEX/MATCH with SEQUENCE:

=SUM(INDEX(C10:N10,,SEQUENCE(MATCH(Text(Today(),"mmmm"),Table10[#Headers],0)-1)))

SEQUENCE will return an array that starts with 1 and ends with the relative position of the match of the month. Then that array is passed to INDEX which in turn returns an array of values in row 10 to SUM.

For older version we can substitue SEQUNCE like this:

=SUMPRODUCT(INDEX(C10:N10,,ROW($Z$1:INDEX($Z:$Z,MATCH(TEXT(TODAY(),"mmmm"),Table10[#Headers],0)-1))))

This uses ROW to return an array of Rows based on a dynamic range.

answered Mar 26, 2024 at 18:19

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Glen Newell Over a year ago

Man i appreciate that, I knew I was close, but there's always so many functions in excel i always seem to forget one. Sequence seems to be what I needed! you are my hero!

Mayukh Bhattacharya · Accepted Answer · 2024-03-26 18:59:22Z

2

Here is another way of doing this, anchor the last month since we need a cumulative sum, and check the present month using MATCH() or XMATCH() function with the volatile TODAY() enclosed within TEXT() function:

=SUM(C10:INDEX(C10:N10,,MATCH(TEXT(TODAY(),"mmmm"),Table10[#Headers],0)-1))

Or, Using XLOOKUP()

=SUM(C10:XLOOKUP(MONTH(TODAY()),MONTH(Table10[[#Headers],[February]:[January]]&0),C10:N10))

edited Mar 26, 2024 at 18:59

answered Mar 26, 2024 at 18:53

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Collectives™ on Stack Overflow

Excel Summing a chart Based on Month

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related