Cellwise Start free

Excel guide · 3 min read

How to calculate cost per hire in Excel

Quick answerCost per hire = (internal recruiting costs + external recruiting costs) / number of hires in the same period. In Excel, with a cost log in A2:E11 and a hires list in G2:I9, both with a quarter column: =SUMIFS(E2:E11,A2:A11,"Q3")/COUNTIFS(G2:G9,"Q3"). This team spent $59,050 to make 7 hires in Q3, about $8,436 per hire.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGHIJKL
1QuarterDepartmentTypeCostAmountHire quarterDepartmentSourceSettingValue
2Q2SalesExternalAgency fee9000Q2SalesAgencyQuarterQ3
3Q3SalesExternalJob ads1800Q3SalesJob board
4Q3SalesExternalAgency fee14400Q3SalesJob board
5Q3EngineeringExternalAgency fee22500Q3SalesAgency
6Q3EngineeringExternalJob ads2600Q3SalesReferral
7Q3SalesExternalReferral bonus2000Q3SalesReferral
8Q3EngineeringInternalRecruiter time9000Q3EngineeringAgency
9Q3SalesInternalRecruiter time6000Q3EngineeringJob board
10Q3EngineeringExternalBackground checks450
11Q3SalesExternalBackground checks300

Download this sheet as an Excel practice file (.xlsx)

Step 1: Log every recruiting cost

A:E is a cost log: quarter, department, type (internal or external) and what the money went on. G:I lists each hire with the quarter they were hired and where they came from. The quarter to report sits in L2, so you change one cell to switch periods.

External costs are paid to outsiders: agency fees, job ads, background checks, referral bonuses, career fairs. Internal costs are your own team's time and tools: recruiter salaries for the hours spent hiring, the applicant tracking system. Internal costs are the ones people forget, and leaving them out makes cost per hire look much lower than it is.

Step 2: Cost per hire for the quarter

Total costs for the quarter divided by hires in the quarter: =SUMIFS(E2:E11,A2:A11,L2)/COUNTIFS(G2:G9,L2). That's $59,050 / 7, about $8,435.71.

The Q2 row (an agency fee of $9,000) and the Q2 hire stay out because both formulas filter on the same quarter. Dividing everything by everything, =SUM(E2:E11)/COUNTA(G2:G9), gives $8,506.25, a number that belongs to no period at all.

=SUMIFS(E2:E11,A2:A11,L2)/COUNTIFS(G2:G9,L2)

Result: 8435.7142857 shows as $8,436 with Currency format

About 8435.71: average recruiting cost per hire in Q3.

By department, and internal vs external

Add a department condition to both sides: =SUMIFS(E2:E11,A2:A11,L2,B2:B11,"Engineering")/COUNTIFS(G2:G9,L2,H2:H9,"Engineering"), $17,275. Sales is $4,900. Engineering made two hires, one through an agency at $22,500, so its average is high. With so few hires, one fee swings the result; look at several quarters before drawing conclusions.

The split by type: =SUMIFS(E2:E11,A2:A11,L2,C2:C11,"External") is $44,050 and the internal total is $15,000. If external costs dominate, agency use is usually why.

=SUMIFS(E2:E11,A2:A11,L2,B2:B11,"Engineering")/COUNTIFS(G2:G9,L2,H2:H9,"Engineering")

Result: 17275 shows as $17,275 with Currency format

17275: Engineering's cost per hire in Q3.

Cost per hire by source

Match each source's direct cost to the hires it produced. Agency: =SUMIFS(E2:E11,A2:A11,L2,D2:D11,"Agency fee")/COUNTIFS(G2:G9,L2,I2:I9,"Agency"), $36,900 / 2 = $18,450. Job board: job ads of $4,400 over 3 hires, about $1,466.67. Referral: $2,000 of bonuses over 2 hires, $1,000.

Recruiter time and background checks are shared across every source, so they're left out here. That's why the source figures don't add up to the overall $8,436: they compare direct costs, which is what you need when deciding where to post the next role.

=SUMIFS(E2:E11,A2:A11,L2,D2:D11,"Agency fee")/COUNTIFS(G2:G9,L2,I2:I9,"Agency")

Result: 18450 shows as $18,450 with Currency format

18450: direct agency cost per agency hire in Q3.

Roles still open, and which date counts

Only people actually hired go in the bottom of the division. A role that's still open isn't a hire, even if you've spent money on it. Under the common approach, money spent in the quarter counts in the quarter, so a quarter with lots of open roles looks expensive and the next one cheap.

Some teams prefer to track cost per requisition and count the costs when the role is filled. Either works; pick one rule, write it next to the table, and apply it every quarter. Also decide which date makes someone a hire: offer accepted or start date. Accepted is common, and it keeps cost per hire in the same period as time to hire.

Common mistakes

Leaving out internal costs

Add recruiter and hiring-manager time and tools. Without them, cost per hire is badly understated.

Mixing periods

Filter costs and hires on the same quarter in both SUMIFS and COUNTIFS.

Counting open roles as hires

Count only people who accepted (or started) in the period.

Expecting source figures to add up to the total

Source figures use direct costs only. Shared costs stay in the overall number.

FAQ

What is the formula for cost per hire?

(Internal recruiting costs + external recruiting costs) / total hires in the same period. In Excel: =SUMIFS(costs,quarters,"Q3")/COUNTIFS(hire_quarters,"Q3").

What costs are included in cost per hire?

External: agency fees, job ads, background checks, referral bonuses, events. Internal: recruiting staff time and tools. Salaries of the new hires themselves are not recruiting costs.

Should referral bonuses count in cost per hire?

Yes, they're an external recruiting cost tied to a hire. Put them in the period they're paid, or the period of the hire if that's your rule, and be consistent.

How do I calculate cost per hire by department?

Add a department condition to both the SUMIFS and the COUNTIFS so the costs and the hires come from the same department and period.

Functions used

Related guides

Updated