Description
Use this function to count the number of empty cells in a specified range of cells. Supported in Chains. Can be used with CHILDREFS.
COUNTBLANK is useful for quickly identifying and tallying the number of blank cells in a dataset.
Syntax
COUNTBLANK(range)
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid input |
|---|---|---|---|
range |
Yes | The range in which to count the blank cells. | A cell range or a formula which results in a cell range. |
Example
Sample data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Value | Price | Margin | Discount |
| 2 | T-shirt | $23.00 | $30.00 | 23.33% | 10% |
| 3 | Candle | $1.20 | $6.00 | 80.00% | 5% |
| 4 | Pillow | ||||
| 5 | Blanket | $12.25 | $36.00 | 65.97% | 20% |
| 6 | Rug | $18.60 | $45.00 | 58.67% | 8% |
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Count the number of cells in a range that are blank. | =COUNTBLANK(B2:B6) |
This formula works in the following manner:
For this example, the formula returns 1. |
| Count the number of cells in a multiple ranges that are blank. | =COUNTBLANK(B2:E6) |
This formula works in the following manner:
For this example, the formula returns 4. |
| See if there are any blank cells in the specified range. If there is at least one blank cell), return "Data missing". Otherwise, return "All cells filled". | =IF(COUNTBLANK(A2:A10)>0, "Data missing", "All cells filled") |
This formula works in the following manner:
For this dataset, this formula returns "Data missing". |
| Count the total number of blank cells across three separate ranges. | =SUM(COUNTBLANK(A2:A10), COUNTBLANK(C2:C10), COUNTBLANK(E2:E10)) |
This formula works in the following manner:
For this dataset, this formula returns 14. |
Notes
- COUNTBLANK only counts truly empty cells, not cells with formulas that return an empty string.
- Cells that appear empty but contain spaces or other non-visible characters are not counted as blank.
Tips
- Use COUNTBLANK to quickly identify missing data in a dataset.
- Combine COUNTBLANK with conditional formatting to highlight missing data in a dataset.
- Use COUNTBLANK to validate data entry, ensuring that required fields are filled.
Related functions
COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
ISBLANK
ISERROR
ISNA
ISNUMBER
VALUE