How to Calculate Absenteeism Rate by Line of Business in WPS Office Excel
Question details
The user needs to calculate absenteeism percentages by line of business from attendance data, excluding days marked OFF or PTO from scheduled-day totals.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user has an attendance table with employee line-of-business and daily statuses such as Present, Absent, OFF, and PTO, and wants a calculated absenteeism breakdown for each line of business.
- Observed behavior
- They need a formula-based way to total absent days and scheduled days per line of business, then divide those totals into absenteeism percentages.
Make sure the attendance data has one Line of Business column and consistent daily status labels such as Present, Absent, OFF, and PTO. Confirm that OFF and PTO mean non-scheduled days for your calculation.
Calculate Absenteeism Rate by Line of Business with SUMPRODUCT in WPS Office Excel
Use this solution when you want a straightforward formula-based summary that works in WPS Office without requiring LAMBDA or BYROW.
This SUMPRODUCT approach is compatible with WPS Office and does not require LET, LAMBDA, or BYROW. It returns an absent-day total and a scheduled-day total for each line of business, then divides the two totals.
In a blank area of the attendance sheet, create four column headers: Line of Business, Absent Days, Scheduled Days, and Absenteeism Rate. Copy the existing Line of Business column and use Data > Remove Duplicates to produce each unique business line.
In the first Absent Days cell, type =SUMPRODUCT(($B$2:$B$100=$E2)*($C$2:$I$100="Absent")) and press Enter. Adjust $B$2:$B$100 to your actual Line of Business column range and $C$2:$I$100 to your daily attendance range.
In the first Scheduled Days cell, type =SUMPRODUCT(($B$2:$B$100=$E2)*($C$2:$I$100<>"OFF")*($C$2:$I$100<>"PTO")) and press Enter. This counts only cells that are not OFF and not PTO.
In the first Absenteeism Rate cell, type =IF(G2=0,"",F2/G2) assuming Absent Days is column F and Scheduled Days is column G. Press Enter, then apply Percentage format from Home > Number Format for the rate column.
Calculate Absenteeism Rate by Line of Business in WPS Office
WPS Office can complete this calculation with built-in spreadsheet formulas. Open the attendance workbook, build a line-of-business summary table, and use SUMPRODUCT to count absent and scheduled days before formatting the result as a percentage.
- 1. Install WPS Office: Download WPS Office from the official WPS site and install the free Office suite on your computer.
- 2. Open the attendance workbook: Launch WPS Office Excel, click Menu > Open, and select your existing .xlsx attendance data file.
- 3. Create the line-of-business summary: In a blank area, add Line of Business, Absent Days, Scheduled Days, and Absenteeism Rate headers. List each unique business line using Data > Remove Duplicates.
- 4. Enter the absenteeism formulas: Use the SUMPRODUCT formulas to count absent days and scheduled days, then divide absent days by scheduled days for each business line.
- 5. Format and review: Select the rate cells, apply the Percentage format from the Home tab, and verify totals for each line of business.
Frequently Asked Questions
Can I calculate absenteeism rates by line of business without using LAMBDA or BYROW in WPS Office?
Yes. Use a SUMPRODUCT summary table: count Absent cells and scheduled cells per line of business with criteria excluding OFF and PTO, then divide the totals.
How do I exclude both OFF and PTO from the scheduled-day total?
Add two criteria to the SUMPRODUCT formula: ($C$2:$I$100<>"OFF") and ($C$2:$I$100<>"PTO"). Blank cells are counted as scheduled; add a blank-cell condition if you do not want blanks to count.
Why does my absenteeism rate show as a decimal instead of a percentage?
Select the rate cells, go to Home > Number Format, and choose Percentage. Use =IF(G2=0,"",F2/G2) to avoid division errors for zero scheduled days.
What should I do if I have a different number of daily attendance columns?
Replace $C$2:$I$100 with the actual range containing daily statuses, and keep the Line of Business range consistent in both the Absent Days and Scheduled Days formulas.
Can WPS Spreadsheets open this attendance workbook later in Microsoft Excel?
Yes. WPS Spreadsheets saves .xlsx files with Microsoft Excel format compatibility, so workbooks with the SUMPRODUCT formula open correctly in Excel.
