Calculate Employee Attrition by Group in Excel with XLOOKUP and SUMIF
Question details
Calculate average employee headcount and attrition percentages for each group using spreadsheet formulas.

- 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.
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.
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.
Enter the group names to report on in F2:F4, such as Group A, Group B, and Group C.
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,"").
In H2, enter =IFERROR(XLOOKUP(F2,$A$2:$A$100,$D$2:$D$100)/G2,"").
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 SUMIF to combine multiple rows per group
Add source values for each group before calculating attrition when a group appears on multiple rows.
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. 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. Create the group list: Enter each group to report on in column F, starting at F2.
- 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. 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.

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.




