While using Excel, if a few values are present in one list but missing from another list, one can use a combination of the formulas COUNTIF and SUMPRODUCT to count the missing values.

## Formula

The generic formula to count missing values is given below:

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

## Explanation

The purpose of COUNTIF function is to check values in a range against the condition. Usually, only a single condition is supplied; however, in some cases, multiple criteria can be supplied. We only want to count missing values (having zero counts), thus “=0” statement is used to convert missing values to TRUE and FALSE.

The double negative (**—**) operator in the formula is used to convert TRUE value to **‘1’** and FALSE value to **‘0’**. The SUMPRODUCT function in the formula adds items in the list and provides a total number of missing values.

## Example

In this example, we have made two lists containing names of different fruits i.e., list 1 and list 2 as shown in the screenshot. In COUNTIF, the range of the list 1 is B6:B11 and list 2 (F6:F8) is the condition or criteria in the example. Since we have provided more than one condition, we get more than one result. The formula in H6 is:

**=SUMPRODUCT(--(COUNTIF(B6:B11,F6:F8)=0))**

The result shows that there are two missing values i.e. Pineapple and Pomegranate because these are absent from the list 1.

*Figure 1 Example of Count Missing Values in Excel*

## Notes

You can also count missing values in Excel by using the following formula which is based on the ISNA function;

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

## Leave a Comment