Sum of Cumulative Quarterly Data

[abschy]

Hi all,

Need some help with an excel formula to calculate the cumulative quarterly YTD data based on whichever quarter is selected.

In my dashboard, i have a cell to select which quarter i want - Q1, Q2, Q3, Q4

What i need is a formula to calculate the cumulative sum based on Quarter:

For eg.
If Q2 is selected, sum Q1 and Q2
If Q4 is selected, sum Q1 - Q4 data.

Currently, i have a column in my data table calculating what Quarter my project lies in based on the date.
But i am unable to come up with a simplified formula to calculate this.
I know i can use "IF" formula, but that is just too long and cumbersome, so i would like to see if there is any other formula that is more streamlined?

Thank you all for your help!

 

[Kerryx]

This is something a pivot table would handle relatively easily.

Book1
	B	C	D	E	F
1	Date	Amt
2	21/01/2022	10
3	21/05/2022	20		Row Labels	Sum of Amt
4	21/02/2022	30		Qtr1	40
5	21/06/2022	40		Qtr4	150
6	21/08/2022	50		Grand Total	190
7	21/09/2022	60
8	21/10/2022	70
9	26/10/2022	80
Sheet1

 

[Kerryx]

Book1
	E	F
2
3	Row Labels	Sum of Amt
4	Qtr1	40
5	Qtr2	60
6	Qtr3	110
7	Qtr4	150
8	Grand Total	360
Sheet1

 

[abschy]

Book1
	E	F
2
3	Row Labels	Sum of Amt
4	Qtr1	40
5	Qtr2	60
6	Qtr3	110
7	Qtr4	150
8	Grand Total	360
Sheet1

Hi Kerryx,

Thanks for your reply.

The pivot table only shows the sum for that quarter itself, not the cummulative sum.

Something like below:

 

[Kerryx]

Ok so basically a runing total of the Qtrs