How to Count missing values in a list in Excel Office 365?
Tags: countmissingvaluesin, excelpreface
Published: 2021-03-20 07:12:19

---
title: How to Count missing values in a list in Excel Office 365?
published: true
date: 2021-03-20 07:11:21 UTC
tags: Countmissingvaluesin,ExcelPreface
canonical_url: https://geekexcel.com/how-to-count-missing-values-in-a-list-in-excel-office-365/
---

Today, we are going to learn the steps to **count missing values in a list in Excel Office 365** with an example. To do this task, we will use [SUMPRODUCT](https://geekexcel.com/how-to-use-excel-sumproduct-function-in-office-365/) and [COUNTIF](https://geekexcel.com/formulas-to-count-with-countif-function-in-excel-office-365/) function. This article will provide the simple steps for you to achieve the task. Get an official version of ** MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

**Check this too:** [Find and Fill Missing Numbers in a Sequence in Excel Office 365 ](https://geekexcel.com/find-and-fill-missing-numbers-in-sequence-in-excel-office-365/)
## Count missing values in a list:

To count the missing data from the specific range in Excel, you need to follow the below steps.

- For instance, you need to **create two lists of items** that are the actual list ( **essential** ) and received items ( **received** ) as shown in the below image.

![Created lists](https://geekexcel.com/wp-content/uploads/2021/03/EXCEL_4ADmvzahDv.png)<figcaption id="caption-attachment-22585">Created lists</figcaption>

- Now, we are going to check the list and get the missing values from it.
- To do this, you can use the following formula.

**=SUMPRODUCT(–(COUNTIF(Range1,Range2)=0))**
- **Range1 –** It is the checklist range in Excel.
- **Range2 –** This is the range that contains the actual list of items.

- You have to enter the formula in the cell where you want to get the result.

![Enter the formula](https://geekexcel.com/wp-content/uploads/2021/03/EXCEL_4UYADv4w8B.png)<figcaption id="caption-attachment-22586">Enter the formula</figcaption>

- After applying the formula, **press** the **Enter** key to see the result as shown in the below image.

![Result](https://geekexcel.com/wp-content/uploads/2021/03/EXCEL_cAPK7Dj5uo.png)<figcaption id="caption-attachment-22587">Result</figcaption>

## Sum-Up:

In the above article, we have demonstrated the simple steps to **count missing values in a list in Excel Office 365** with an example. Give your **worthwhile**  **feedback** in the below comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)**

**Related Posts:**

- [Excel Formulas to Highlight Cells that begin with a Given Criteria!!](https://geekexcel.com/excel-formulas-highlight-cells-that-begin-with-given-criteria/)
- [Excel Formulas to Create Gantt Chart with Time Schedule!!](https://geekexcel.com/excel-formulas-to-create-gantt-chart-with-time-schedule/)
- [How to Compare Two Ranges using Macros (VBA) in Excel 365?](https://geekexcel.com/how-to-compare-two-ranges-using-macros-vba-in-excel-365/)
- [Hide Rows with Zero Value using Macros (VBA) in Excel Office 365!](https://geekexcel.com/hide-rows-with-zero-value-using-macros-vba-in-excel-office-365/)
- [Use Data Validation with No Punctuation in Excel Office 365!!](https://geekexcel.com/use-data-validation-with-no-punctuation-in-excel-office-365/)