Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel FORMULATEXT function
  • How to use Excel VLOOKUP Function
  • Perform case-sensitive Lookup in Excel
  • How to retrieve first match between two ranges in Excel
  • How to use Excel COLUMN Function

Data Analysis Examples

  • How to Create Column Chart in Excel
  • How To Create Pareto Chart in Excel
  • How To Insert and Customize Sparklines in Excel
  • How to Create Gantt Chart in Excel
  • How To Perform and Interpret Regression Analysis in Excel

Data Validation Examples

  • Excel Data validation date in specific year
  • Excel Data validation specific characters only
  • Excel Data validation don’t exceed total
  • Excel Data validation allow uppercase only
  • Excel Data validation unique values only

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.

Worked Example:   Get work hours between dates custom schedule in Excel

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}

Worked Example:   Highlight duplicate columns in Excel

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}

Worked Example:   Excel Data validation specific characters only

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:

Dynamic lookup table with INDIRECT in Excel

Next Post:

Case sensitive match 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
  • How to create email address from name in Excel
  • How to split text with delimiter in Excel
  • How to calculate Number of Instances in Excel
  • Get last name from name with comma — Manipulating NAMES in Excel
  • How to check if cell contains all of many things in Excel
  • Excel Date & Time Functions Example
  • How to calculate working days left in month in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • TIME function: Description, Usage, Syntax, Examples and Explanation
  • Two ways to sum time over 30 minutes in Excel
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • ODDLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles