How to count records active on a given date in Excel
Formula (Excel and Google Sheets)
=COUNTIFS(B2:B11,"<="&G1,C2:C11,">="&G1)+COUNTIFS(B2:B11,"<="&G1,C2:C11,"")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,C2:C11,">="&G1)+COUNTIFS(B2:B11,"<="&G1,C2:C11,"").
- Result on the example
- 5 in
G2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
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.
=COUNTIFS(B2:B11,"<="&G1,C2:C11,">="&G1)+COUNTIFS(B2:B11,"<="&G1,C2:C11,"")Result5
B2:B1110 rows × 1 col: 11/1/2025, 12/15/2025, 1/10/2026, 2/1/2026, …A range: 10 cells in one column.
G13/16/2026The value in G1.
"<="&G1"<=46097"The & operator joins as text.
C2:C1110 rows × 1 col: 2/28/2026, (empty), 3/15/2026, 3/31/2026, …A range: 10 cells in one column.
">="&G1">=46097"The & operator joins as text.
COUNTIFS(B2:B11,"<="&G1,C2:C11,">="&G1)3COUNTIFS: Counts rows that meet every condition.- criteria_range1
B2:B1110 rows × 1 col: 11/1/2025, 12/15/2025, 1/10/2026, 2/1/2026, … - criteria1
"<="&G1"<=46097" - criteria_range2
C2:C1110 rows × 1 col: 2/28/2026, (empty), 3/15/2026, 3/31/2026, … - criteria2
">="&G1">=46097"
- criteria_range1
"<="&G1"<=46097"The & operator joins as text.
COUNTIFS(B2:B11,"<="&G1,C2:C11,"")2COUNTIFS: Counts rows that meet every condition.- criteria_range1
B2:B1110 rows × 1 col: 11/1/2025, 12/15/2025, 1/10/2026, 2/1/2026, … - criteria1
"<="&G1"<=46097" - criteria_range2
C2:C1110 rows × 1 col: 2/28/2026, (empty), 3/15/2026, 3/31/2026, … - criteria2
""
- criteria_range1
COUNTIFS(B2:B11,"<="&G1,C2:C11,">="&G1)+COUNTIFS(B2:B11,"<="&G1,C2:C11,"")5The + operator adds.
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS(B2:B11,"<="&G1,C2:C11,">="&G1)+COUNTIFS(B2:B11,"<="&G1,C2:C11,""). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B11<=G1)*((C2:C11>=G1)+(C2:C11="")>0)) | 5 | One test: started by the date, and either ends later or has no end date. |
| =COUNTIFS(B2:B11,"<="&G1,C2:C11,">="&G1) | 3Different result | 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.
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 records active on a given date
G2, count the memberships that were active on the date in G1; a blank end date means still active.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.
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.