Count Number of Cells With Non-Matching Values

[masouder]

I have a list of valid names on one sheet. On another sheet, users manually enter names. Ignoring the fact that I can use Data Validation to ensure all entered names are valid, how can I count the number of cells on the second sheet that contain invalid names?

 

[Eric W]

Try:

Book2
	A	B	C	D	E
1	Valid Names		Entered Names		# of invalid names
2	Al		Chloe		3
3	Betty		Erica
4	Chloe		Ed
5	Daniel		Mike
6	Erica		Al
7	Fred		Betty
8			Frank
9			Chloe
10
Sheet2

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT((C2:C15<>"")*ISERROR(MATCH(C2:C15,A2:A7,0)))

 

[masouder]

Eric,

Perfect, thanks. I was trying to make COUNTIF work with VLOOKUP within ISERROR and it just wasn't working. Should have thought of SUMPRODUCT.

Michael Souder

 

[Eric W]

Happy to help!