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% |
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:
Result: 1. |
| Count the number of cells in a multiple ranges that are blank. | =COUNTBLANK(B2:E6) |
This formula works in the following manner:
Result: 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:
Result: "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:
Result: 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.