I'm making a list of people who have participated in a two part assessment, & want to track total analytics of how many people have passed the assessment vs how many have failed.

My worksheet looks something like this

In this case, we know that 3 people passed and 2 others failed the assessment.

I know I can find the last entry for each student and check if they passed or failed individually using

=LOOKUP(2,1/($B:$B="George"),$D:$D)

but that would use an extra column & take up unnecessary space. (I know I can hide columns) What I would like to do is lookup the users who did pass and count them.

So... my question is, how can I combine =CountIF() with =Lookup() to count how many students have passed?

CodePudding user response：

If you have access to the UNIQUE function, you could use the following formula to get the number of FALSE for each individual student:

=COUNTIFS(Pass?,FALSE,StudentID,UNIQUE(StudentID))
'i.e. {0;1;1;0;0}

To get the ones that passed, use SUMPRODUCT:

=SUMPRODUCT(--(COUNTIFS(Pass?,FALSE,StudentID,UNIQUE(StudentID))=0))
'i.e. =SUMPRODUCT(--({0;1;1;0;0}=0)) => 3

Update: explainer formula

In this formula UNIQUE(StudentID) is getting you an array with the unique students in order of appearance: {"George";"Brandon";"Daniel";"Tahlia";"Jane"}

Next, COUNTIFS(Pass?,FALSE,StudentID,UNIQUE(StudentID) gets us the count of FALSE for each individual student, also as an array: {0;1;1;0;0}. I.e. Zero FALSE for "George", "Tahlia" and "Jane", 1 FALSE for "Brandon" and "Daniel".

Finally, we simply would have wanted to do a COUNTIFS to count all zeros. However, the results are in an array, and COUNTIF(S) does not work with an array. This is why we use SUMPRODUCT with the "double-negative" (double unary) operation. This operation coerces TRUE/FALSE to 1/0, so we are getting --({0;1;1;0;0}=0) => {TRUE,FALSE,FALSE,TRUE,TRUE} => {1,0,0,1,1}. Now, SUMPRODUCT multiplies and sums this final array, leading to 3.

(N.B. In the current example, the final array is simply the inverse of the input array. This is so, because no student had more than one FALSE. But if a student had FALSE twice, the final array would not be different.)

CodePudding user response：

If the reality of your context is that you only need to count people who have passed stage 2 then you could use the formula in C12 below

=SUM(--((C2:C10)*(D2:D10)=2))

(this would have to be entered as an array formula if you're using Excel 2019 or earlier)