Sum difference cell range based on single criteria

[philipmartin]

What formula can I use to sum year-to-date data when I update the "Current Month" as my criteria?


Current Month: MAR
[TABLE="width: 645"]
<colgroup><col span="12"><col></colgroup><tbody>[TR]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]JUL[/TD]
[TD]AUG[/TD]
[TD]SEP[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]YTD Total[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]100[/TD]
[TD] ??[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
My "YTD Total" result for MAR would be 400.

If I changed current month to May, my result should be 600.

 

[SteveO59L]

are the entries formatted dates, rather than text?

or

if you don't mind adding another row for a running total (which could be hidden)

 

[philipmartin]

I could do the second option, but was hoping there might be a clean formula I just couldn’t think of.

 

[FDibbins]

Try this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][/tr]
[tr][td]

1​

[/td][td]Current Month:[/td][td]May[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]JAN[/td][td]FEB[/td][td]MAR[/td][td]APR[/td][td]MAY[/td][td]JUN[/td][td]JUL[/td][td]AUG[/td][td]SEP[/td][td]OCT[/td][td]NOV[/td][td]DEC[/td][td]YTD Total[/td][/tr]

[tr][td]

3​

[/td][td]

100​

[/td][td]

150​

[/td][td]

150​

[/td][td]

100​

[/td][td]

100​

[/td][td]

150​

[/td][td]

150​

[/td][td]

100​

[/td][td]

100​

[/td][td]

150​

[/td][td]

150​

[/td][td]

100​

[/td][td]

600​

[/td][/tr]
[/table]
M3=SUM(A3:INDEX($B$3:$L$3,MATCH($B$1,$A$2:$L$2,0)-1))

 

[philipmartin]

It works! You guys are geniuses, thanks!

 

[FDibbins]

Thanks for the kind words (it helps if you know what tools to use lol)