How to count rows where one column is filled and another is blank in Excel
Formula (Excel and Google Sheets)
=COUNTIFS(B2:B11,"<>",C2:C11,"")Combine "not blank" and "blank" in COUNTIFS: =COUNTIFS(B2:B11,"<>",C2:C11,"").
- Result on the example
- 4 in
F1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.
How it works
"<>" keeps rows where the Sent date is filled in, and "" keeps rows where Paid is empty. COUNTIFS needs both, so drafts that were never sent (INV-03 and INV-07) are not counted as unpaid. Four invoices are waiting for payment.
=COUNTIFS(B2:B11,"<>",C2:C11,"")Result4
B2:B1110 rows × 1 col: 3/2/2026, 3/3/2026, (empty), 3/5/2026, …A range: 10 cells in one column.
C2:C1110 rows × 1 col: 3/10/2026, (empty), (empty), 3/20/2026, …A range: 10 cells in one column.
COUNTIFS(B2:B11,"<>",C2:C11,"")4COUNTIFS: Counts rows that meet every condition.- criteria_range1
B2:B1110 rows × 1 col: 3/2/2026, 3/3/2026, (empty), 3/5/2026, … - criteria1
"<>" - criteria_range2
C2:C1110 rows × 1 col: 3/10/2026, (empty), (empty), 3/20/2026, … - criteria2
""
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS(B2:B11,"<>",C2:C11,""). We ran it in Google Sheets mode on the same data and it returns 4.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B11<>"")*(C2:C11="")) | 4 | The same count with SUMPRODUCT, for combining with tests COUNTIFS can't do, such as a date difference. |
| =COUNTBLANK(C2:C11) | 6Different result | Counts every invoice without a paid date. Also counts the two drafts that were never sent. |
Common mistakes
- Counting blanks in the Paid column alone includes rows that never started, like unsent drafts.
- A cell with a space is not blank, so
""won't match it. Clean imported data with TRIM.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count rows where one column is filled and another is blank
F1, count the invoices that have a sent date but no paid date.Questions
How do I add up the unpaid amounts?
Use SUMIFS with the same conditions: =SUMIFS(D2:D11,B2:B11,"<>",C2:C11,"").
How do I count unpaid invoices older than 30 days?
Add a date condition: =COUNTIFS(B2:B11,"<"&TODAY()-30,C2:C11,""); a date test already excludes blanks.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.