Get financial month's quarter from date

[redspanna]

Hey all

NDA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	DATE	MONTH	Q	SALES			2021/22	2022/23		MONTH	N	Q
2	03-Sep-21	Sep	2	600		Q1	0	1625		JAN	1	4
3	14-Dec-21	Dec	3	299		Q2	600	0		FEB	2	4
4	15-Jan-22	Jan	4	1725		Q3	299	275		MAR	3	4
5	01-Feb-22	Feb	4	1425		Q4	4550	0		APR	4	1
6	25-Feb-22	Feb	4	1075						MAY	5	1
7	12-Mar-22	Mar	4	325						JUN	6	1
8	12-Jun-22	Jun	1	1500						JUL	7	2
9	19-Jun-22	Jun	1	125						AUG	8	2
10	02-Sep-22	Sep	2	0						SEP	9	2
11	03-Dec-22	Dec	3	275						OCT	10	3
12										NOV	11	3
13										DEC	12	3
14
15
Data (3)

Cell Formulas
Range	Formula
B2:B11	B2	=IF(ISBLANK(A2),"",TEXT(A2,"MMM"))
C2:C11	C2	=IF(ISBLANK(A2),"",VLOOKUP(B2,$J$2:$L$13,3,FALSE))

My Data as shown in this sample consists of a list of dates in COLUMN A , the month for that date in COLUMN B, a formula to get the quater number in COLUMN C and the sales totals for that date in COLUMN D

Is there a formula that can generate the totals as shown in cells G2:H5 that will show the totals sales for each financial quarter in a certain year, like year 2021/22 and 2022/23 as shown?

Hope this makes sense and apprecite in advance

 

[gordsky]

This should do what you need. Paste it in to all cells g2 to h5.
You then just need to adjust the "C2:C11=1" to be your quarterly number (where the 1 represents the quarter in the below code) for each cell
Do same for column H but and then adjust "Right($G$1,2*1" to "Right($H$!,2*1" for column H

=SUMPRODUCT((RIGHT((YEAR($A$2:$A$11)+(MONTH($A$2:$A$11)>=4)),2)*1<=RIGHT($G$1,2)*1)*($C$2:$C$11=1)*($D$2:$D$11))

breakdown of code is as follows

(RIGHT((YEAR($A$2:$A$11)+(MONTH($A$2:$A$11)>=4)),2)*1

gets the phys cal year for the Q

<=RIGHT($G$1,2)*1

checks for match to your year in G or H

($C$2:$C$11=1)

looks for which Q (in this case Q1)

($D$2:$D$11

refers to totals column

 

[redspanna]

@gordsky
many thanks for your help - works great, and appreciate the detailed explanation too!

Cheers