Sum values before date that changes

[kanemitchell99]

Hi,

Simple task but my brain aint working. I want to sum the total cost before and after a certain date. So if the date is 30/04/2023 sum the total row before this date (should include April month).
When I change the date it should then obviously update the cost.

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Today	2023-04-30		Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023
2	Spend B4	323,129			0	96,040	0	18,999	0	212,873	120,747	36,349
3	Spend Aft	1,212,449			0	0	227,089	56,101	0	0	157,710	247,362
4					0	0	0	118,086	29,416	0	80,816	133,990
5				0	0	96,040	227,089	193,186	29,416	212,873	359,273	417,701
Sheet7

Cell Formulas
Range	Formula
E1:L1	E1	=EDATE(D1,1)
B2	B2	=SUMIFS($D$5:$L$5,$D$1:$L$1,"<="&B1)
B3	B3	=SUMIFS($D$5:$L$5,$D$1:$L$1,">"&B1)
D5:L5	D5	=SUM(D2:D4)

 

[Coyotex3]

Another option :

SUM(FILTER(D2:L4,D1:L1<=B1))

=SUM(FILTER(D2:L4,D1:L1>B1))

 

[awoohaw]

Another option :

SUM(FILTER(D2:L4,D1:L1<=B1))

=SUM(FILTER(D2:L4,D1:L1>B1))

Ah, FILTER is in 2021. Good to remember.

 

[kanemitchell99]

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Today	2023-04-30		Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023
2	Spend B4	323,129			0	96,040	0	18,999	0	212,873	120,747	36,349
3	Spend Aft	1,212,449			0	0	227,089	56,101	0	0	157,710	247,362
4					0	0	0	118,086	29,416	0	80,816	133,990
5				0	0	96,040	227,089	193,186	29,416	212,873	359,273	417,701
Sheet7

Cell Formulas
Range	Formula
E1:L1	E1	=EDATE(D1,1)
B2	B2	=SUMIFS($D$5:$L$5,$D$1:$L$1,"<="&B1)
B3	B3	=SUMIFS($D$5:$L$5,$D$1:$L$1,">"&B1)
D5:L5	D5	=SUM(D2:D4)

Thank you. I knew it could be done with SUMIFS but didn't know to properly use the "<="&B1 part. Cheers!

 

[awoohaw]

Thank you. I knew it could be done with SUMIFS but didn't know to properly use the "<="&B1 part. Cheers!

My pleasure,
Best wishes.

 

[Coyotex3]

Ah, FILTER is in 2021. Good to remember.

I actually Googled it prior to posting the solution because I could not remember