Count with Multiple Criteria

[holyrollyp]

Hello Excel Experts,

I need your advice please on how I can do my count in Excel using multiple row and column criteria. I already checked online but I could only see two criteria, while my case has 3. I cannot make it work. Below is the sample data I am working on and the the last table is what I was trying to achieve.

I have one cell for Country and it has a dropdown for United States, Japan, Australia and SIngapore.
if I chose a country, I want to count the no of criteria for each employee.
Example. for United States, James have 2 Membership issue, 1 Quality Issue and 1 system issue.
I would like to do this count for all employees per country and per criteria. can you help with the right formula please?

Employee	Criteria	Country
James	Quality Issue	United States
Marc	Quality Issue	Australia
Kevin	Membership Issue	Japan
Diana	System Issue	United States
Vincent	Membership Issue	Singapore
Mary	Pricing Issue	Japan
Mary	Pricing Issue	Singapore
James	System Issue	United States
James	Membership Issue	Japan
Marc	System Issue	United States
Marc	Quality Issue	Australia
Marc	Membership Issue	Australia
Vincent	System Issue	Japan
Vincent	Membership Issue	United States
James	Quality Issue	Singapore
James	Pricing Issue	Singapore
Kevin	System Issue	United States
James	System Issue	Singapore
Kevin	Pricing Issue	United States
Mary	Membership Issue	Japan
Mary	Quality Issue	United States
Diana	Quality Issue	Australia
Diana	Quality Issue	Japan
Marc	Membership Issue	Singapore
Marc	System Issue	United States
Vincent	Quality Issue	Japan
Vincent	Membership Issue	United States
James	System Issue	Singapore
James	Membership Issue	United States
James	System Issue	Japan
James	Membership Issue	United States

Country	United States
	Membership Issue	Pricing Issue	Quality Issue	System Issue
Diana				1​
James	2​		1​	1​
Kevin		1​		1​
Marc				2​
Mary			1​
Vincent	2​

 

[kevin9999]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Employee	Criteria	Country
2	James	Quality Issue	United States			Country	Singapore
3	Marc	Quality Issue	Australia
4	Kevin	Membership Issue	Japan				Membership Issue	Pricing Issue	Quality Issue	System Issue
5	Diana	System Issue	United States			Diana
6	Vincent	Membership Issue	Singapore			James		1	1	2
7	Mary	Pricing Issue	Japan			Kevin
8	Mary	Pricing Issue	Singapore			Marc	1
9	James	System Issue	United States			Mary		1
10	James	Membership Issue	Japan			Vincent	1
11	Marc	System Issue	United States
12	Marc	Quality Issue	Australia
13	Marc	Membership Issue	Australia
14	Vincent	System Issue	Japan
15	Vincent	Membership Issue	United States
16	James	Quality Issue	Singapore
17	James	Pricing Issue	Singapore
18	Kevin	System Issue	United States
19	James	System Issue	Singapore
20	Kevin	Pricing Issue	United States
21	Mary	Membership Issue	Japan
22	Mary	Quality Issue	United States
23	Diana	Quality Issue	Australia
24	Diana	Quality Issue	Japan
25	Marc	Membership Issue	Singapore
26	Marc	System Issue	United States
27	Vincent	Quality Issue	Japan
28	Vincent	Membership Issue	United States
29	James	System Issue	Singapore
30	James	Membership Issue	United States
31	James	System Issue	Japan
32	James	Membership Issue	United States
Sheet1

Cell Formulas
Range	Formula
G5:J10	G5	=COUNTIFS($A$2:$A$32,$F5,$B$2:$B$32,G$4,$C$2:$C$32,$G$2)

Cells with Data Validation
Cell	Allow	Criteria
G2	List	United States,Japan,Australia,Singapore

 

[holyrollyp]

Wow, that was fast. Thanks so much, i will teat this tomorrow and will give an update. I am positibw this is already what I was looking for. Have a great weekend, Kevin, thanks so much again,

 

[kevin9999]

Wow, that was fast. Thanks so much, i will teat this tomorrow and will give an update. I am positibw this is already what I was looking for. Have a great weekend, Kevin, thanks so much again,

I look forward to hearing back from you after testing, and welcome to the board!

 

[Snakehips]

@holyrollyp Welcome.

If by any chance you would want a dynamic list, featuring only the employees having issues relating to the country in question then, try @kevin9999 's solution with the single additional formula in F5.

Book3
	A	B	C	D	E	F	G	H	I	J
1	Employee	Criteria	Country
2	James	Quality Issue	United States			Country	Japan
3	Marc	Quality Issue	Australia
4	Kevin	Membership Issue	Japan				Membership Issue	Pricing Issue	Quality Issue	System Issue
5	Diana	System Issue	United States			Kevin	1	0	0	0
6	Vincent	Membership Issue	Singapore			Mary	1	1	0	0
7	Mary	Pricing Issue	Japan			James	1	0	0	1
8	Mary	Pricing Issue	Singapore			Vincent	0	0	1	1
9	James	System Issue	United States			Diana	0	0	1	0
10	James	Membership Issue	Japan
11	Marc	System Issue	United States
12	Marc	Quality Issue	Australia
13	Marc	Membership Issue	Australia
14	Vincent	System Issue	Japan
15	Vincent	Membership Issue	United States
16	James	Quality Issue	Singapore
17	James	Pricing Issue	Singapore
18	Kevin	System Issue	United States
19	James	System Issue	Singapore
20	Kevin	Pricing Issue	United States
21	Mary	Membership Issue	Japan
22	Mary	Quality Issue	United States
23	Diana	Quality Issue	Australia
24	Diana	Quality Issue	Japan
25	Marc	Membership Issue	Singapore
26	Marc	System Issue	United States
27	Vincent	Quality Issue	Japan
28	Vincent	Membership Issue	United States
29	James	System Issue	Singapore
30	James	Membership Issue	United States
31	James	System Issue	Japan
32	James	Membership Issue	United States
Sheet3

Cell Formulas
Range	Formula
F5:F9	F5	=UNIQUE(FILTER(A:A,C:C=$G$2,""))
G5:J20	G5	=IF(F5="","",COUNTIFS($A$2:$A$32,$F5,$B$2:$B$32,G$4,$C$2:$C$32,$G$2))
Dynamic array formulas.

Hope that helps.

 

[holyrollyp]

@holyrollyp Welcome.

If by any chance you would want a dynamic list, featuring only the employees having issues relating to the country in question then, try @kevin9999 's solution with the single additional formula in F5.

Book3
	A	B	C	D	E	F	G	H	I	J
1	Employee	Criteria	Country
2	James	Quality Issue	United States			Country	Japan
3	Marc	Quality Issue	Australia
4	Kevin	Membership Issue	Japan				Membership Issue	Pricing Issue	Quality Issue	System Issue
5	Diana	System Issue	United States			Kevin	1	0	0	0
6	Vincent	Membership Issue	Singapore			Mary	1	1	0	0
7	Mary	Pricing Issue	Japan			James	1	0	0	1
8	Mary	Pricing Issue	Singapore			Vincent	0	0	1	1
9	James	System Issue	United States			Diana	0	0	1	0
10	James	Membership Issue	Japan
11	Marc	System Issue	United States
12	Marc	Quality Issue	Australia
13	Marc	Membership Issue	Australia
14	Vincent	System Issue	Japan
15	Vincent	Membership Issue	United States
16	James	Quality Issue	Singapore
17	James	Pricing Issue	Singapore
18	Kevin	System Issue	United States
19	James	System Issue	Singapore
20	Kevin	Pricing Issue	United States
21	Mary	Membership Issue	Japan
22	Mary	Quality Issue	United States
23	Diana	Quality Issue	Australia
24	Diana	Quality Issue	Japan
25	Marc	Membership Issue	Singapore
26	Marc	System Issue	United States
27	Vincent	Quality Issue	Japan
28	Vincent	Membership Issue	United States
29	James	System Issue	Singapore
30	James	Membership Issue	United States
31	James	System Issue	Japan
32	James	Membership Issue	United States
Sheet3

Cell Formulas
Range	Formula
F5:F9	F5	=UNIQUE(FILTER(A:A,C:C=$G$2,""))
G5:J20	G5	=IF(F5="","",COUNTIFS($A$2:$A$32,$F5,$B$2:$B$32,G$4,$C$2:$C$32,$G$2))
Dynamic array formulas.

Hope that helps.

Tnx snakehips,
That’s a great idea, too. However, for now, my country list is fixed and will always be those 4 countries. i will surely try your formula as well, and will keep it for future use. Thanks!

 

[Fluff]

Another option so that the formula spills, is

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Employee	Criteria	Country
2	James	Quality Issue	United States			Country	Japan
3	Marc	Quality Issue	Australia
4	Kevin	Membership Issue	Japan				Membership Issue	Pricing Issue	Quality Issue	System Issue
5	Diana	System Issue	United States			Kevin	1	0	0	0
6	Vincent	Membership Issue	Singapore			Mary	1	1	0	0
7	Mary	Pricing Issue	Japan			James	1	0	0	1
8	Mary	Pricing Issue	Singapore			Vincent	0	0	1	1
9	James	System Issue	United States			Diana	0	0	1	0
10	James	Membership Issue	Japan
11	Marc	System Issue	United States
12	Marc	Quality Issue	Australia
13	Marc	Membership Issue	Australia
14	Vincent	System Issue	Japan
15	Vincent	Membership Issue	United States
16	James	Quality Issue	Singapore
17	James	Pricing Issue	Singapore
18	Kevin	System Issue	United States
19	James	System Issue	Singapore
20	Kevin	Pricing Issue	United States
21	Mary	Membership Issue	Japan
22	Mary	Quality Issue	United States
23	Diana	Quality Issue	Australia
24	Diana	Quality Issue	Japan
25	Marc	Membership Issue	Singapore
26	Marc	System Issue	United States
27	Vincent	Quality Issue	Japan
28	Vincent	Membership Issue	United States
29	James	System Issue	Singapore
30	James	Membership Issue	United States
31	James	System Issue	Japan
32	James	Membership Issue	United States
33
Sheet4

Cell Formulas
Range	Formula
F5:F9	F5	=UNIQUE(FILTER(A2:A1000,C2:C1000=$G$2,""))
G5:J9	G5	=COUNTIFS(A:A,F5#,B:B,G4:J4,C:C,G2)
Dynamic array formulas.

 

[holyrollyp]

COUNTIFS($A$2:$A$32,$F5,$B$2:$B$32,G$4,$C$2:$C$32,$G$2)

Hello @kevin9999,
Thanks very much! The formula works. I just change it a bit so that if the cell is 0, i would like it to be "" instead of the 0 value.
Appreciate your help.
Have a great and safe day ahead.

 

[kevin9999]

Hello @kevin9999,
Thanks very much! The formula works. I just change it a bit so that if the cell is 0, i would like it to be "" instead of the 0 value.
Appreciate your help.
Have a great and safe day ahead.

Glad I could help & thanks for the feedback