This article explains how to count cells that meet specific criteria using the COUNTIFS formula in Microsoft Excel. In this example, we will count cells that are blank and have specific values in other cells.
Understanding the COUNTIFS Formula
The COUNTIFS formula in Excel allows you to count cells that meet multiple criteria. It takes the following syntax:
=COUNTIFS(range1, criteria1, [range2, criteria2], ...)
In our case, we will use it to count cells that are blank and have a specific value in another cell.
Counting Blank Cells with Specific Values
Let's assume we have the following data:
| Column A | Column B |
|---|---|
| John | 10 |
| Mary | |
| Bob | 5 |
| Alice | 20 |
We want to count the number of cells in Column B that are blank and have the value "10" in Column A.
Step 1: Select the Range
First, we need to select the range of cells we want to count. In this case, it is the range of cells in Column B that meet our criteria.
Step 2: Write the Formula
Now, we can write the formula. In our example, the formula would be:
=COUNTIFS(A2:, "10", B2:, "")
This formula counts the number of cells in the range B2: that are blank and have the value "10" in the range A2:
Step 3: Check the Result
When we enter the formula in a cell, it will return the number of blank cells in Column B that have the value "10" in Column A. In our example, the result would be "1".
- The COUNTIFS formula in Excel allows you to count cells that meet multiple criteria.
- To count blank cells with specific values, use the COUNTIFS formula with an empty string ("") as the criteria for the blank cells.
References: