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.
The generic formula to count missing values is given below:
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.
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:
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
You can also count missing values in Excel by using the following formula which is based on the ISNA function;