Sum formula change when you go into a new month

[mickyd67]

Might be an easy one, but I've got a mental block

Looking for a formula that will move to the next column of data when we move into a new month

• i.e. the current formula in Column N is =SUM(B1:J1)
• When we get to next Tuesday I need to change that manually to =SUM(B1:K1)

Easy when it's one line of data like below, but I have a file with multiple calculations & rows so trying to find a formula driven solution

Is there a way to do it?

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	YTD
2		5	10	15	20	25	30	35	40	45	50	55	60	225
Sheet1

Cell Formulas
Range	Formula
N2	N2	=SUM(B2:INDEX(B2:M2,MATCH(TEXT(TODAY(),"mmm"),$B$1:$M$1,0)))

 

[Fluff]

How about

=SUM(B2:INDEX(B2:M2,,MONTH(TODAY())))

 

[mickyd67]

Hi both - thanks a lot for replying. Apologies it took me so long to get back.

I got both versions to work, which is great.

I do have a couple of caveats though...

What would the formula need to be if I wanted to have this calculation happen when my year doesn't run from Jan to Dec, but a fiscal year from April to March?

Also - what if I wanted to look at demand YTD but also include the next month, and another formula to include the following month so I would have

Demand YTD
Demand YTD+1
Demand YTD+2

 

[Fluff]

What would the formula need to be if I wanted to have this calculation happen when my year doesn't run from Jan to Dec, but a fiscal year from April to March?

Phuoc's formula should already do that.