Active Headcount by Month

Excel Formulas › HR & Payroll

All versionsSUMPRODUCT

Count how many employees were active in a given month from their hire and termination dates — an employee counts if they were hired on or before the month end and not yet terminated. SUMPRODUCT does it without a helper column.

Quick formula: count staff active as of a month-end date:
=SUMPRODUCT((hire<=month_end)*((term="")+(term>month_end)))
Hired by month end AND (still employed OR terminated after month end) marks each active employee.

Functions used (tap for the full reference guide):

SUMPRODUCT

The example

Active as of June 30.

AB
1EmployeeActive?
2Hired 1/4, no termyes
3Term 5/2no

The formula

The formula:

=SUMPRODUCT((C2:C50<=E1)*((D2:D50="")+(D2:D50>E1))) // hired by, not yet gone

How it works

How it works:

  1. hire <= month_end marks everyone hired on or before the month-end date.
  2. (term="") + (term>month_end) marks anyone still employed or terminated after the month end.
  3. Multiplying the two TRUE/FALSE arrays keeps only employees who satisfy both conditions.
  4. SUMPRODUCT adds the 1s — the active headcount for that month.

Build a monthly trend by putting month-end dates across a row (use EOMONTH) and copying the formula with the month-end reference relative. You get a headcount-by-month series ready to chart — great for turnover and growth dashboards.

Try it: interactive demo

Live demo

Month-end date to count active staff (sample of 5).

Active headcount:

Variations

Hires in a month

New starts:

=SUMPRODUCT((hire>=m_start)*(hire<=m_end))

Terminations in a month

Leavers:

=SUMPRODUCT((term>=m_start)*(term<=m_end))

Active right now

Current count:

=SUMPRODUCT((hire<=TODAY())*(term=""))

Pitfalls & errors

Blank terminations. Active employees have an empty term cell — the (term="") test handles them.

Real dates. Hire/term must be date serials, not text, for the comparisons to work.

Boundary rule. Decide whether someone terminated on the month-end counts — adjust <= vs < accordingly.

Practice workbook

📊
Download the free Active Headcount by Month practice workbook
A headcount sheet with the hires, terminations, and current-count variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count active employees by month in Excel?
Use =SUMPRODUCT((hire<=month_end)*((term="")+(term>month_end))). It counts everyone hired by the month end who hadn't yet left.
How do I build a headcount trend across months?
Put EOMONTH dates across a row and copy the formula with a relative month-end reference to get a monthly series to chart.
How do I count current active staff?
Use =SUMPRODUCT((hire<=TODAY())*(term="")) — hired by today and no termination date.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: SUMPRODUCT formula · Count dates in range · Employee tenure

Function references: SUMPRODUCT