Formula for SUM updates done Quarterly

[ukanez]

January

February

March

Q1

April

May

June

Q2

July

August

September

Q3

October

November

December

Q4

YTD

2020

2020

2020

2020

2020

2020

2020

2020

2020

2020

2020

2020

2020

2020

2020

2020

2020

Apples

10​

10​

10​

30​

10​

10​

10​

30​

10​

10​

10​

30​

10​

10​

10​

30​

50​

Hey everyone. I'm trying to figure out a formula for the Q1-Q4 Columns that will dynamically sum based on the current date and quarter so that I have a correct YTD sum at the end.

Right now my YTD formula is SUM(B4:INDEX(B4:Q4,MATCH("current month",$B$1:$Q$1,0))). Originally I did not have quarter columns so this formula worked great for me because it would automatically update the YTD based on the month.

However now that I have these quarter columns I am getting a YTD sum that is double counting the quarter columns.

Example: It is currently May so YTD Apple sales should be 50. Instead however my YTD formula returns 80 which is 50 + 30 from Q1 column.

My attempt at fixing it: SUM(B4:INDEX(B4:Q4,MATCH("current month",$B$1:$Q$1,0)))-Sum(E4,I4,M4,Q4). However now I get 80 - 120 = -40 which is also incorrect. The Idea I am trying to figure out now is if there is a way to set Q1, Q2, Q3, and Q4 as dynamic sums that will only add up if the dates match. Example: Q2 will be 10 in April, 20 in May, and then 30 in June. Q3 will be 0 in June, 10 in July, 20 in August etc.

Sorry I hope this is clear, let me know if I need to explain further. I am not married to my current idea for a solution so if you have alternative, easier implementations that would work for me as well.

Thank you for your help.

 

[AhoyNC]

Would using the SUBTOTAL function work for you?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		January	February	March	Q1	April	May	June	Q2	July	August	September	Q3	October	November	December	Q4	YTD
2		2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020
3
4	Apples	10	10	10	30	10	10	10	30	10	10	10	30	10	10	10	30	120
5	Pears			20	20		15		15				0				0	35
Sheet3

Cell Formulas
Range	Formula
Q4:Q5, M4:M5, I4:I5, E4:E5	Q4	=SUBTOTAL(9,N4:P4)
R4:R5	R4	=SUBTOTAL(9,B4:Q4)

 

[ukanez]

Thanks, the SUBTOTAL function helps ignore the quarterly columns, but the problem I'm having is that the YTD column should be dynamic based on the current month. Is there anyway to fit SUBTOTAL into an INDEX MATCH formula so that the SUBTOTAL grabs a dynamic range based on the month?

Example: We're in May so the YTD for Apple sales should be 50 and not 120 which is the total YTD.

 

[AhoyNC]

Why would you have values in months above May? If no value in the months June - Dec. then SUBTOTAL should work.

 

[Phuoc]

Try this in B3: =IF(LEFT(B1,1)="Q","",--(1&B1&B2)) and copy to the right.

In R4:

=SUMIFS(B4:Q4,$B$3:$Q$3,"<=" &TODAY())

 

[AhoyNC]

If there will be values past the current month and your headers are text and not dates formatted as month. Then try the formula in R4 for YTD.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		January	February	March	Q1	April	May	June	Q2	July	August	September	Q3	October	November	December	Q4	YTD
2		2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020
3
4	Apples	10	10	10	30	10	10	10	30	10	10	10	30	10	10	10	30	50
5	Pears			20	20		15		15				0				0	35
Sheet3

Cell Formulas
Range	Formula
Q4:Q5, M4:M5, I4:I5, E4:E5	Q4	=SUBTOTAL(9,N4:P4)
R4:R5	R4	=SUBTOTAL(9,B4:INDEX(B4:Q4,0,MATCH(TEXT(TODAY(),"mmmm"),$B$1:$Q$1,0)))

 

[ukanez]

Hey sorry, yeah that's my fault. Technically this formula should be for 2019 where I have a full year of Apple sales.

The SUBTOTAL using an Index Match works perfectly. Thank you for the help!