# How to count rows where one column is filled and another is blank in Excel

Combine "not blank" and "blank" in COUNTIFS: `=COUNTIFS(B2:B11,"<>",C2:C11,"")`.

**Excel:** `=COUNTIFS(B2:B11,"<>",C2:C11,"")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Invoice | Sent | Paid |  | Sent, not paid |
| INV-01 | 2026-03-02 | 2026-03-10 |  |  |
| INV-02 | 2026-03-03 |  |  |  |
| INV-03 |  |  |  |  |
| INV-04 | 2026-03-05 | 2026-03-20 |  |  |
| INV-05 | 2026-03-09 |  |  |  |
| INV-06 | 2026-03-11 |  |  |  |
| INV-07 |  |  |  |  |
| INV-08 | 2026-03-16 | 2026-03-18 |  |  |
| INV-09 | 2026-03-18 |  |  |  |
| INV-10 | 2026-03-23 | 2026-03-30 |  |  |

Formula in F1: `=COUNTIFS(B2:B11,"<>",C2:C11,"")`

Result: 4

## 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.

## Other ways

- `=SUMPRODUCT((B2:B11<>"")*(C2:C11=""))`: The same count with SUMPRODUCT, for combining with tests COUNTIFS can't do, such as a date difference.
- `=COUNTBLANK(C2:C11)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-rows-where-one-column-is-filled-and-another-is-blank