Function Problems

Calculate Employee Attrition by Group in Excel with XLOOKUP and SUMIF

Steve KSteve K Nov 18, 2026 869 views

Question details

Calculate average employee headcount and attrition percentages for each group using spreadsheet formulas.

Calculate Employee Attrition by Group in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains starting headcount, ending headcount, and leaver counts for groups such as A, B, and C.
Observed behavior
The desired result is an average headcount and an attrition percentage for each group.
Before you start

Check that starting headcount, ending headcount, and leaver counts cover the same reporting period. Identify whether each group has one source row or several rows that need to be added together.

Solution 1Recommended

Use XLOOKUP when each group has one source row

Retrieve the headcount and leaver values for each group, then calculate its average headcount and attrition rate.

This example assumes group names are in A2:A100, starting headcounts in B2:B100, ending headcounts in C2:C100, and leaver counts in D2:D100. XLOOKUP returns the first match, so use this method only when each group has one relevant row.

1
List the groups

Enter the group names to report on in F2:F4, such as Group A, Group B, and Group C.

2
Calculate average headcount

In G2, enter =IFERROR((XLOOKUP(F2,$A$2:$A$100,$B$2:$B$100)+XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100))/2,"").

3
Calculate attrition

In H2, enter =IFERROR(XLOOKUP(F2,$A$2:$A$100,$D$2:$D$100)/G2,"").

4
Fill and format the results

Drag the fill handles in G2 and H2 down alongside the group list. Select the attrition results in column H and choose Home > Number > Percent Style.

Use XLOOKUP when each group has one source row
Check blank results: IFERROR leaves the result blank if a group is not found or its average headcount is zero. Check the group name and source values if a result is blank.
Calculate attrition in WPS Spreadsheets

Calculate Group Attrition with SUMIF in WPS Spreadsheets

Use SUMIF in WPS Spreadsheets to total group headcounts and leavers, then calculate and format each group's attrition percentage.

  1. 1. Open the worksheet: Open WPS Spreadsheets and use Open to select your workbook. Put group names in column A, starting headcounts in B, ending headcounts in C, and leaver counts in D.
  2. 2. Create the group list: Enter each group to report on in column F, starting at F2.
  3. 3. Calculate average headcount: In G2, enter =(SUMIF($A$2:$A$100,F2,$B$2:$B$100)+SUMIF($A$2:$A$100,F2,$C$2:$C$100))/2.
  4. 4. Calculate and format attrition: In H2, enter =IFERROR(SUMIF($A$2:$A$100,F2,$D$2:$D$100)/G2,""). Copy G2:H2 down for the other groups, then select the attrition cells and apply Percentage format from the Home tab.
Calculate attrition for multiple groups in one worksheetOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and percentage formatting
microsoft office alternative - wps office

Frequently Asked Questions

What is the formula for employee attrition?

Divide the number of leavers during the reporting period by the average headcount. In this method, average headcount is (starting headcount + ending headcount) / 2. Format the result as a percentage.

Can XLOOKUP add all rows for a group?

No. XLOOKUP returns the first matching value by default. Use SUMIF to add values from multiple rows for the same group.

Why is my attrition result blank?

The formulas use IFERROR to hide errors. Check that the group name matches the source data and that the average headcount is not zero.

Can I use these formulas in WPS Spreadsheets?

Yes. Open the workbook in WPS Spreadsheets and use the SUMIF formulas to calculate average headcount and attrition. Apply Percentage format from the Home tab to the attrition cells.