count if cell values NOT in column 2

[flashbbeb]

Hi all,

I'm trying to figure out how to get a count of cells in a column (all are strings) that do NOT include the values in a specific range. The range is specified in a separate column to the left.

This is a list of about 50000 names of courses (exact repeats included), 17 of which (the range) I do not want counted.

I've tried different variations of DCOUNT and COUNTIF, but nothing works.

Any thoughts?

Thanks,
EB

 

[SkipVought]

Hi,

Do a MATCH on the "specific range"

assuming that you list is in column A, beginning in row 2...
[tt]
=if(isna(MATCH(A2,specific range ref,0)),0,1)
[/tt]
SUM the results to get a count.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[flashbbeb]

Thanks, Skip. That's a decent solution, however, I want to avoid having to create a new column to do the sum.

Is there any way to check the column with courses (column C, 50000) against a range of the 17 course names - and have it count all in one cell?

Thanks,
EB

 

[flashbbeb]

Here's a one cell solution that's not as elegant as I would have hoped for...

=sum(countif($C:$C,B2),countif(C:$C,B3),countif(C:$C,B4),countif(C:$C,B5),
countif(C:$C,B6),countif(C:$C,B7),countif(C:$C,B8),countif(C:$C,B9),
countif(C:$C,B10),countif(C:$C,B11),countif(C:$C,B12),countif(C:$C,B13),
countif(C:$C,B14),countif(C:$C,B15),countif(C:$C,B16),countif(C:$C,B17))

 

[xlhelp]

=COUNTA(C:C)-SUM(COUNTIF(C:C,B1:B17))

and enter it as an array formula (that is, type the formula and press CTRL/SHIFT/ENTER)

I don't particularly care about apathy

 

[flashbbeb]

That's one elegant solution.

Thanks xlhelp!