How to Count Absences and Presence by Role in WPS Office Excel
Question details
Aggregate attendance status codes such as A and P into counts per employee and role from a worksheet.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has employee roles and daily status entries and needs absence and presence totals by role and by individual resource.
- Observed behavior
- The worksheet stores A and P codes across date columns, but the totals are not yet calculated.
Open the workbook in WPS Office Spreadsheet and verify that status codes use consistent values such as A for absence and P for presence, and that employee roles are stored in a separate column.
Count absence and presence totals for each employee with COUNTIF
Use this when each employee occupies one row and the daily status codes are stored in a continuous range such as B2:Z2.
COUNTIF counts cells that match one criterion and is the fastest way to total A or P codes in a single row.
Click in an empty cell in the same row, for example AA2, to hold the absence total.
Type =COUNTIF(B2:Z2,"A") and press Enter to count absence codes in that employee's date range.
In another blank cell, type =COUNTIF(B2:Z2,"P") and press Enter to count presence codes.
Select both formula cells and drag the fill handle down through all employee rows.
Count absence and presence totals by role with COUNTIFS
Use this when your data is normalized with Role, Resource, Date, and Status columns, or when you need a role-level summary.
Count Attendance by Role and Status in WPS Office Spreadsheet
WPS Office Spreadsheet supports COUNTIF, COUNTIFS, and SUMPRODUCT, so you can open your existing workbook, add summary formulas, and calculate attendance totals without changing your file format.
- 1. Download and install WPS Office: Go to the WPS Office download page, download WPS Office, and install it on your computer.
- 2. Open the attendance workbook: Launch WPS Office Spreadsheet and open the .xlsx or .xls file that contains the role and status data.
- 3. Add summary cells: Click a blank cell in the employee row or role summary block where you want the absence and presence totals.
- 4. Enter the count formulas: Type =COUNTIF(B2:Z2,"A") for individual absence totals or =COUNTIFS($A$2:$A$100,A2,$D$2:$D$100,"A") for role-based absence totals, then press Enter.
- 5. Save or export the result: Save the file as .xlsx to preserve formulas and continue editing in WPS Office or Microsoft Excel.
Frequently Asked Questions
Can COUNTIF distinguish uppercase A from lowercase a?
No, COUNTIF is not case-sensitive. It treats uppercase A and lowercase a as the same criterion. If case-sensitive counting is required, use =SUMPRODUCT(--EXACT(B2:Z2,"A")).
What should I do if absence status uses more than one code, such as A and L?
Add two COUNTIF functions together for individual totals: =COUNTIF(B2:Z2,"A")+COUNTIF(B2:Z2,"L"). For role-level counts, add two COUNTIFS formulas with the corresponding status criteria.
Can I use these formulas if my table has roles in one column and dates across columns?
Yes. Use =COUNTIF(B2:Z2,"A") for each individual absence total. For role totals in that wide layout, use =SUMPRODUCT(($A$2:$A$100=A2)*(B2:Z100="A")) and copy it down for each role.
Does WPS Office support COUNTIF and COUNTIFS like Microsoft Excel?
Yes. WPS Office Spreadsheet supports COUNTIF, COUNTIFS, SUMPRODUCT, and most Excel functions, and it can open and save .xlsx files without conversion.
