Count Based on Quarter

[kumara_faith]

Hi,

I have the following table:

Table 1				Table 2
Year	Month	Sales Staff		Year	Q1	Q2	Q3	Q4
2021	Jan	Mary		2021	4	3	1	2
2021	Jan	John		2022	1	2	1
2021	Feb	Kelly
2021	Mar	Clancy
2021	Apr	Maret
2021	May	Juarez
2021	Jun	Martin
2021	Jul	Amy
2021	Aug
2021	Sep
2021	Oct
2021	Nov	Huang
2021	Dec	Luke
2022	Jan
2022	Feb
2022	Mar	Numez
2022	Apr	Andrian
2022	May
2022	Jun	Joe
2022	Jul
2022	Aug	Manny
2022	Sep
2022	Oct
2022	Nov
2022	Dec

Table 1 is the raw data. In Table 2, I am trying to summarize the number of sales staff based on quarter for year 2021 and 2022. Is there a formula that I could use to achieve this as the actual raw data is very long.

Appreciate any help.

 

[bebo021999]

18.xlsm
	A	B	C	D	E	F	G	H	I
1	Table 1				Table 2
2	Year	Month	Sales Staff		Year	Q1	Q2	Q3	Q4
3	2021	Jan	Mary		2021	4	3	1	2
4	2021	Jan	John		2022	1	2	1	0
5	2021	Feb	Kelly
6	2021	Mar	Clancy
7	2021	Apr	Maret
8	2021	May	Juarez
9	2021	Jun	Martin
10	2021	Jul	Amy
11	2021	Aug
12	2021	Sep
13	2021	Oct
14	2021	Nov	Huang
15	2021	Dec	Luke
16	2022	Jan
17	2022	Feb
18	2022	Mar	Numez
19	2022	Apr	Andrian
20	2022	May
21	2022	Jun	Joe
22	2022	Jul
23	2022	Aug	Manny
24	2022	Sep
25	2022	Oct
26	2022	Nov
27	2022	Dec
Sheet2

Cell Formulas
Range	Formula
F3:I4	F3	=SUMPRODUCT(--(DATEVALUE(1&$B$3:$B$27&$A$3:$A$27)<=EOMONTH(DATE($E3,MATCH(F$2,$F$2:$I$2,0)*3,1),0))*($C$3:$C$27<>"")*($A$3:$A$27=$E3))-SUM($E3:E3)+$E3

 

[kumara_faith]

Hi bebo,

Thank you for the solution and it worked. Appreciate it and have a great day ahead.