< Go Back

Count missing values

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)))

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar