Count the number of Ys and Ns by Role

[Manny74]

Hello All,

So I am trying to get a:

• grand total count of Ys by 1 (1 is a RN) ....result should be 3
• grand total count of Ns by 1 (1 is a RN) ...result should be 6
• grand total count of Ys by 2 (2 is a MD)....result should be 1
• grand total count of Ns by 2 (2 is a MD) ...result should be 2

Instead of manually counting this, I was thinking of using Countif formulas?
But wasn't sure how to piece this together....

	A1	B2	C2	D2
	1 for RN, 2 for MD	Q1 (Compliant: Y or N)	Q2 (Compliant: Y or N)	Q3 (Compliant: Y or N)
A3	1​	N	N	Y
A4	2​	N	Y	N
A5	1​	N	Y	N
A6	1​	Y	N	N
	1 (RN)	6 N	3 Y
	2 (MD)	2 N	1 Y

 

[Eric W]

How about:

Book1
	A	B	C	D
1
2	1 for RN, 2 for MD	Q1 (Compliant: Y or N)	Q2 (Compliant: Y or N)	Q3 (Compliant: Y or N)
3	1	N	N	Y
4	2	N	Y	N
5	1	N	Y	N
6	1	Y	N	N
7
8	Role	N	Y
9	1	6	3
10	2	2	1
Sheet10

Cell Formulas
Range	Formula
B9:C10	B9	=SUMPRODUCT(($B$3:$D$6=B$8)*($A$3:$A$6=$A9))

 

[Manny74]

How about:

Book1
	A	B	C	D
1
2	1 for RN, 2 for MD	Q1 (Compliant: Y or N)	Q2 (Compliant: Y or N)	Q3 (Compliant: Y or N)
3	1	N	N	Y
4	2	N	Y	N
5	1	N	Y	N
6	1	Y	N	N
7
8	Role	N	Y
9	1	6	3
10	2	2	1
Sheet10

Cell Formulas
Range	Formula
B9:C10	B9	=SUMPRODUCT(($B$3:$D$6=B$8)*($A$3:$A$6=$A9))

Hello Eric, that worked, thank you so much! Do I dare ask, is there a simpler way of getting the same end result, like using a Countif(s), etc.? Just wondering, as I am not too familiar with SUMPRODUCTs

 

[Eric W]

Without SUMPRODUCT, you'd need to do something like this:

Book1
	A	B	C	D
1
2	1 for RN, 2 for MD	Q1 (Compliant: Y or N)	Q2 (Compliant: Y or N)	Q3 (Compliant: Y or N)
3	1	N	N	Y
4	2	N	Y	N
5	1	N	Y	N
6	1	Y	N	N
7
8	Role	N	Y
9	1	6	3
10	2	2	1
Sheet3

Cell Formulas
Range	Formula
B9:C10	B9	=COUNTIFS($A$3:$A$6,$A9,$B$3:$B$6,B$8)+COUNTIFS($A$3:$A$6,$A9,$C$3:$C$6,B$8)+COUNTIFS($A$3:$A$6,$A9,$D$3:$D$6,B$8)

You need a separate COUNTIFS for each column.