Count based on multiple conditions

[braedenbear]

I feel like this is a simple question but the solution eludes me.

I'm trying to accomplish 2 things:

1. Count the total number of people in a region who have:
   1. Completed all the training courses
   2. Completed some of the training courses
   3. Completed none of the training course
2. Generate a list of people in each of the categories above.

I've played around with a few formulas but have a hard time establishing the "count by unique person" condition. Any help or tips would be appreciated!

 

[Wookiee]

While this isn't as elegant a solution as I would have liked, I believe it returns the results you want to see.

First, I put your sample data in cells A1:D13. In cells J5:M5, I put the description headers. In J6, I used the UNIQUE function to extract a list of unique names.

The formulas I entered in row 6 (and subsequently copied down) were:

J6	=UNIQUE(A2:A13)
K6	=IF(COUNTIFS($A$2:$A$13,$J6,$C$2:$C$13,"Yes")=3, "Yes","")
L6	=IF(AND(COUNTIFS($A$2:$A$13,$J6,$C$2:$C$13,"Yes")>0, COUNTIFS($A$2:$A$13,$J6,$C$2:$C$13,"Yes")<3),"Yes","")
M6	=IF(COUNTIFS($A$2:$A$13,$J6,$C$2:$C$13,"Yes")=0, "Yes","")

Then, to get the counts, I added Count headers in row 3 and the following formula in cell K4 (dragging it to cell M4):

=COUNTIF(K$6:K$9,"Yes")

(Honestly, i had forgotten about the counting portion after working out all the initial calculations )

 

[braedenbear]

@Wookiee this is perfect, much appreciated. I think I was trying to do too much in one go - needed to separate out the unique names first, then build the formulas as you have done above.

Thanks again