Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Rules in Excel
  • Working With Tables in Excel
  • Number and Text Filters Examples in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • Move chart to a separate worksheet in Excel

References

  • How to use Excel OFFSET function
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Create hyperlink with VLOOKUP in Excel
  • How to use Excel LOOKUP Function
  • How to reference named range different sheet in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation allow uppercase only
  • Data validation must not exist in list
  • Excel Data validation don’t exceed total
  • Excel Data validation must not contain

Count missing values in Excel

by

This tutorial shows how to calculate Count missing values in Excel using the example below;

Formula

=SUMPRODUCT(--(COUNTIF(list1,list2)=0))

Explanation

To count the values in one list that are missing from another list, you can use a formula based on the COUNTIF and SUMPRODUCT functions.

In the example shown, the formula in H6 is:

=SUMPRODUCT(--(COUNTIF(list1,list2)=0))

Which returns 1 since the value “Osborne” does not appear in B6:B11.

How this formula works

The COUNTIF functions checks values in a range against criteria. Often, only one criteria is supplied, but in this case we supply more than one criteria.

For range, we give COUNTIF the named range list1 (B6:B11), and for criteria, we provide the named range list2 (F6:F8).

Because we give COUNTIF more than one criteria, we get more than one result in a result array that looks like this: {2;1;0}

We want to count only values that are missing, which by definition have a count of zero, so we convert these values to TRUE and FALSE with the “=0” statement, which yields: {FALSE;FALSE;TRUE}

Then we force the TRUE FALSE values to 1s and 0s with the double-negative operator (–), which produces: {0;0;1}

Finally, we use SUMPRODUCT to add up the items in the array and return a total count of missing values.

Alternative with MATCH

If you prefer more literal formulas, you can use the formula below, based on MATCH, which literally counts values that are “missing” using the ISNA function:

=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Check multiple cells are equal in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel NOT Function
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel

Date Time

  • Convert decimal minutes to Excel time
  • How to calculate nth day of week in month in Excel
  • How to get same date next month or previous month in Excel
  • Convert text to date in Excel
  • Calculate date overlap in days in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • Running count group by n size in Excel
  • Group numbers at uneven intervals in Excel
  • If cell contains one of many things in Excel
  • Group arbitrary text values in Excel

General

  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
  • How to calculate decrease by percentage in Excel
  • Basic error trapping example in Excel
  • Lock Cells in a Worksheet Excel
  • How to calculate project complete percentage in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning