Home > Blockchain >  CountIF true on last occurence for list
CountIF true on last occurence for list

Time:05-13

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

Date StudentID Part 1 or 2? Pass?
01/01/2022 George 1 True
01/01/2022 Brandon 1 True
01/01/2022 Daniel 1 False
01/01/2022 Tahlia 1 True
01/01/2022 Jane 1 True
02/01/2022 George 2 True
02/01/2022 Brandon 2 False
02/01/2022 Tahlia 2 True
02/01/2022 Jane 2 True

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

enter image description here

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

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

  • Related