Multiple Criteria

[kumara_faith]

Hi,

I have the following table:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2	Table 1						Table 3								Table 4						Table 5
3	Name	ID No	Policy	Branch	Date		Name	Branch	ID Card	Jan	Feb	Mar	Apr		Payment Type	Jan	Feb	Mar	Apr		Branch	Jan	Feb	Mar	Apr
4	Amy	55487	PRM55877	Dallas	1/1/2022		Amy		55487	0					Premium						Dallas
5	Bryan	66678	NB44785	New York	2/2/2022		Bryan		66678						Loan						New York
6	John	45487	LOA55547	Chicago	3/2/2022		John		45487						New Business						Chicago
7	Javier	25568	PRM5544	Seattle	4/4/2022		Javier		25568												Seattle
8	Javier	45464	NB446558	Boston	1/1/2022		Javier														Boston
9
10	Table 2
11	Code	Description
12	PRM	Premium
13	LOA	Loan
14	NB	New Business
Sheet1

Cell Formulas
Range	Formula
K4	K4	=COUNTIFS(B4:B8,H4,F4:F8,K3)
V4:V8	V4	=UNIQUE(E4:E8)
J4:J7	J4	=XLOOKUP(H4,$B$4:$B$8,$C$4:$C$8)
Dynamic array formulas.

1) Table 1 is the raw data
2) Table 2 is the code to identify the payment description. Example: In Table 1, the column for Policy shows that there is an abbreviation infron of the policy no. This identifies the payment type.
3 Table 3- I am trying to summarize based on the columns. For the name column, I tried using Unique but its not accurate because Javier has sales in both Seattle and Boston. In addition I am not sure how to populate the branch and the count of policies by month
4) Table 4- I am trying to summarize based on the columns. Payment type is derived from the codes in Table 2. This is the abbreviation used in the policies for Table 1. Example PRM0001 means Premium. I am not sure how to derive the count based on the data in Table 1.
5) Table 5 - I am trying to summarize based on the columns. I tried using Countifs but I am not sure how to specify the count by month based on the date in Table 1.

Appreciate any help.

 

[kumara_faith]

Hi Alex,

Thank you so much for your solution. It shows the correct result.

One question. The raw or the source data is in Table 1. Is there any reason why the formula in Table 5 is also referencing Table 3?

 

[Alex Blakenburg]

One question. The raw or the source data is in Table 1. Is there any reason why the formula in Table 5 is also referencing Table 3?

Thats a great catch. The reference to column J should be to V.
So the formula in W4 should be the below then copy or fill down and then across.

=COUNTIFS($E$4:$E$8,$V4,$F$4:$F$8,"<=" & EOMONTH(DATEVALUE("01-"&W$3&"2022"),0),$F$4:$F$8,">" & EOMONTH(DATEVALUE("01-"&W$3&"2022"),-1))

 

[kumara_faith]

Thank you Alex. Appreciate your time and patience. Have a great day ahead.

 

[Alex Blakenburg]

Glad I could help. You too.