How to count the number of values in one list that do not appear in another list

[jk2391]

Hello,

I have two lists of people with a # score on a test.

In the attached workbook I highlight in yellow a cell where I need: The count of people in column 'B' that scored a 50 or lower which do not appear in the list of people in column 'E'.
-The correct output should be 2, since 3 total people in 'B' don't appear in column 'E', however, only 2 of them scored a 50 or below.

I imagine this is some sort of COUNTIF or SUMPRODUCT, but I can't seem to get the logic correct.

Any help would be great - Thanks!

[Keebellah]

I added a helper column in D that is 0 when there is no match found in E for the name

[jk2391]

Thank you - this works, but do you know of a feasible way to calculate the same thing without the use of a helper column?

In the actual dataset I'm working with, an added helper column would corrupt my file, for a few different reasons.

[Keebellah]

You could try and use te formula in that column as an extra formula criterium

[HansDouwe]

Here is a solution without an helper, please try

Please Login or Register  to view this content.

[Rick Rothstein]

Since you are using XL365, this formula should give you the result you want...

=SUM(ISERROR(MATCH(B6:B13,E6:E10,0))*(C6:C13<=50))

[Keebellah]

You got your answers

[alansidman]

an alternative is with Power Query. Filter out those greater than 50 in each table then join the two tables in a Left Anti Join.

Please Login or Register  to view this content.

[CARACALLA]

E2=sumproduct((isna(match(b6:b100,e6:e100,0)))*(c6:c100<=50)*(c6:c100<>""))

[jk2391]

Thank you for the help!