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

Answer 1

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.

Answer 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))

---