Excel COUNTA Practice Question: Using the customer table, how many rows have a name in column A but no status…
The question
Using the customer table, how many rows have a name in column A but no status in column B? Put it in D2.
The data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Status | Result | |
| 2 | Asha | Paid | ? | |
| 3 | Ravi | |||
| 4 | Pending | |||
| 5 | Karan | Paid | ||
| 6 | Divya | |||
| 7 | ||||
| 8 | Imran | Pending | ||
| 9 | Sunita | Paid |
The yellow cell is where your answer goes.
Show answer
Answer in D2: 2
How it works
Explanation: Multiplying two conditions works like AND: name present and status empty.
Syntax: =SUMPRODUCT((A2:A9<>"")*(B2:B9=""))
Ravi and Divya have a name but no status, so 2.
Try it yourself in the live sheet
Type your formula in the highlighted cell and get instant feedback. Free, no Excel install needed.
Try this question in the live sheet →COUNTA syntax
=COUNTA(value1, [value2], ...)
Counts how many cells in a range are not empty. COUNTA is used in data analysis work to summarize or test patterns across a data set. Full COUNTA guide.
More COUNTA practice questions
- Customer names are in A2:A9 (some rows are empty). How many names are listed? Put it in…
- Using the same table, how many customers already have a status in B2:B9? Put it in D2.
- Quantities are in B2:B9. Some cells hold the text n/a and some are empty. How many cells…
- Using the customer table, find the share of rows that have a name (A2:A9), as a decimal…
- Attendance is marked P in B2:C7 (two days) and left empty when absent. How many P marks…
- The records start under a header in row 1. Count the customer records in column A…
- Using the attendance table, how many students attended on at least one day? Put it in E2.
All practice questions · Excel exercises · Interview prep · Cheat sheet