How to Count Employees by Department in Excel

Someone in the leadership meeting asks how many people are in Sales, and you're staring at a 400-row roster with a Department column and no quick answer. You could build a PivotTable, drag the fields, and format it, again, like you did last month and the month before. To count employees by department in Excel without rebuilding that PivotTable every time, a one-click macro gives you the headcount, and the total salary, for every department in a single pass.

This is the reporting version of the job, aimed at anyone in HR or operations who needs the numbers fast, using a free macro for the summary and Excel's built-in functions where they already fit.

TL;DR: count employees by department in Excel

• Clean the roster first. Dedupe it and check for blank departments, or your counts will be wrong.
• Run the summary macro. It groups by department and returns a count (your headcount) and a sum.
• Read two numbers per department. The count is your headcount, the sum is your total salary or FTE.
• Use COUNTIF for a live count. When you want the number to update as the roster changes.

With the macro loaded this takes about 30 seconds. The slow part is trusting the roster, which is why the cleanup comes first.

Get the roster ready first

A headcount is only as good as the list under it. Before you count anything, make sure each employee appears once and every row has a department.

Two quick checks. Dedupe the roster with Remove Duplicates by Multiple Columns so nobody is counted twice, matching on employee ID or name plus start date. Then select the Department column and run Highlight Blank Cells: any blank department is someone who won't land in the right group. If your roster is genuinely messy, run the full employee list cleanup first.

Step 1: Count employees by department

Select your data including the header row and run Auto Pivot Summary. It asks for two column numbers: the column to group by and a numeric column to total, like 3,5 to group by department and sum salary. New to running macros? The 30-second setup gets you going.

It builds a Summary sheet with one row per department, and here's the useful part: it gives you both the count and the sum. The count is your headcount per department. The sum is the total salary, or total FTE if that's the column you point it at. So one pass answers "how many people" and "how much payroll" for every department at once, which is more than a plain count gives you. It overwrites any existing sheet named Summary, so rename an old one first.

Just want the count, with no salary column?

The macro needs a numeric column to total. If your roster has no salary or FTE column and you only want the headcount, add a helper column filled with the number 1, then point the macro's sum at that column. Every row contributes 1, so the sum equals the count and you get a clean headcount per department. Delete the helper column afterward.

The formula alternative: COUNTIF and COUNTIFS

For a count that updates live as the roster changes, use the built-in COUNTIF function. List your department names in a column, then next to each: =COUNTIF(Dept,"Sales"), where Dept is your department column. It recalculates the moment someone is added or removed.

Need to count on two conditions at once? COUNTIFS handles it: =COUNTIFS(Dept,"Sales",Status,"Full-Time") counts only full-time people in Sales. The formulas win when the roster changes often. The macro wins for a one-shot monthly snapshot, because it hands you the count and the salary total together without any setup.

The hot take: headcount and FTE are not the same number

A department with ten people isn't necessarily ten full-time-equivalents. Six full-timers and eight half-time staff is fourteen heads but ten FTE, and reporting one as the other is how a team looks overstaffed on a headcount and understaffed on capacity, or the reverse.

So decide which question you're answering before you report the number. Counting rows gives you heads. Summing an FTE column gives you capacity. The macro can do both in the same pass, count for heads and sum for FTE, which is exactly why having both columns in the summary beats a bare count that hides the distinction.

Common pitfalls

• Blank departments. A row with no department gets grouped as "(blank)" or missed entirely. Fill those in before you count, or your totals won't add up to your total headcount.
• Inconsistent department names. "Sales" and "sales" merge fine because the match ignores case, but "Sales" and "Sales Team" count as two departments. Standardize the names first.
• Duplicated employees. One person on two rows inflates their department by one. Dedupe before counting.
• Counting contractors as headcount. Decide whether contractors and interns belong in the number, and filter them out first if they don't.

Frequently asked questions

How do I count employees by department in Excel?

Select your roster with its header row and run the Auto Pivot Summary macro, grouping by the department column and summing a numeric column like salary. It returns a summary sheet where the count per department is your headcount. For a formula that updates live, use =COUNTIF(DepartmentColumn,"Sales") for each department instead.

How do I get a headcount and total salary per department at once?

Run the summary macro and point it at the department column to group by and the salary column to sum. It returns both the count and the sum for every department in one pass, so you get headcount and total payroll side by side without building a PivotTable or writing two separate formulas.

How do I count employees when there's no number column to sum?

Add a helper column filled with the value 1, then have the macro sum that column. Since every row adds 1, the sum equals the number of rows, giving you a clean headcount per department. Remove the helper column once you have the summary. Alternatively, use COUNTIF, which needs no helper column.

How do I count employees by two things, like department and status?

Use COUNTIFS. For example, =COUNTIFS(Dept,"Sales",Status,"Full-Time") counts only full-time employees in the Sales department. Add as many field-and-value pairs as you need. It's the right tool when a single count has to satisfy more than one condition at the same time.

Do these macros work on Excel for Mac?

Yes. The summary, dedupe, and highlight macros all run on Excel 2016 and later on both Windows and Mac. They avoid Windows-only features, so an HR team on mixed machines gets identical results either way.

What to do next

Clean the roster, run the summary, and the headcount question stops needing a fresh PivotTable every month.

To count employees by department in Excel on your own roster, browse the full set of Excel tools for HR and recruiters, or start with Auto Pivot Summary grouped by your department column.