# How to count records active on a given date in Excel

Count records that started on or before the date and end on or after it, plus those with no end date: `=COUNTIFS(B2:B11,"="&G1)+COUNTIFS(B2:B11,"<="&G1,C2:C11,"")`.

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Member | Start | End |  |  | On date | 2026-03-16 |
| M-01 | 2025-11-01 | 2026-02-28 |  |  | Active |  |
| M-02 | 2025-12-15 |  |  |  |  |  |
| M-03 | 2026-01-10 | 2026-03-15 |  |  |  |  |
| M-04 | 2026-02-01 | 2026-03-31 |  |  |  |  |
| M-05 | 2026-03-01 |  |  |  |  |  |
| M-06 | 2026-03-16 | 2026-06-30 |  |  |  |  |
| M-07 | 2026-03-20 |  |  |  |  |  |
| M-08 | 2026-04-01 |  |  |  |  |  |
| M-09 | 2025-10-01 | 2026-03-16 |  |  |  |  |
| M-10 | 2026-03-10 | 2026-03-12 |  |  |  |  |

Formula in G2: `=COUNTIFS(B2:B11,"<="&G1,C2:C11,">="&G1)+COUNTIFS(B2:B11,"<="&G1,C2:C11,"")`

Result: 5

## How it works

A membership is active on a date if it started on or before it and ends on or after it. The first COUNTIFS counts those with an end date. The second counts the open-ended ones: started by the date, with the End cell empty. Adding them gives 5.

M-06 starts on the date and M-09 ends on it; both count. M-03 ended the day before.

## Other ways

- `=SUMPRODUCT((B2:B11<=G1)*((C2:C11>=G1)+(C2:C11="")>0))`: One test: started by the date, and either ends later or has no end date.
- `=COUNTIFS(B2:B11,"<="&G1,C2:C11,">="&G1)`: Only records with an end date. (Misses the two open-ended memberships.)

## Common mistakes

- Blank end dates don't match `">="&G1`, so open-ended records drop out unless you count them separately, as the second COUNTIFS does, or fill the blanks with a far-future date.
- Decide whether the end date is the last active day or the first inactive one; this formula treats it as the last active day.

## Questions

**How do I count active records for each month end?**

Put the dates in a column and point G1 at it: fill the formula down with `$B$2:$B$11` and `$C$2:$C$11` locked.

**How do I count rooms occupied on a night?**

The same idea, but checkout day isn't occupied: use `">"&G1` on the checkout column.

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-records-active-on-a-date