Jasper Sommer
Reputation: 67
Getting the number of values not in a list
I'm trying to have a function count the number of values that are not present in a list. 
I've tried using both =SUMPRODUCT(--ISNA(MATCH(J2:J4,H2:H10,0))) and =SUMPRODUCT(--(COUNTIF(H2:H10,J2:J4)=0)) but both just display zeroes.
Upvotes: 1
Views: 850
Answers (1)
Scott Craner
Reputation: 152545
Flip the references:
=SUMPRODUCT(--ISNA(MATCH(H2:H10,J2:J4,0)))
Or
=SUMPRODUCT(--(COUNTIF(J2:J4,H2:H10)=0))
Upvotes: 2
Related Questions
- Get the Count only values available in Specific list
- In Excel, How to Exclude a list of Values in COUNTIF
- Count values in a column that are not in a list - excel
- Excel COUNTIFS value not in a list
- Google spreadsheet countif non empty cells except certain values
- Count number of rows where value in a column is not a part of a list
- Count how many in range are not blank or in list
- Count number of cells that don't contain a value in Google Spreadsheets
- Count rows that have cell don't existing in a list in Excel
- Count the number of values in a range that are not defined in a list