Count Weekly Attendance by Job Role in WPS Spreadsheets
Question details
The user needs to calculate weekly attendance counts by job role in WPS Spreadsheets, covering doctors, nurses, and midwives between July 1, 2023, and June 30, 2024.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an attendance log with dates, employee roles, and attendance statuses and wants weekly totals by role for a fiscal year.
- Observed behavior
- The COUNTIFS summary cannot be built until the attendance data layout is known; sample date, role, status, and desired output columns are required.
Prepare your attendance data with separate columns for Date, Employee Role, and Attendance Status, and confirm that all date entries are real date values rather than text before creating the formula.
Use COUNTIFS with date boundaries, role criteria, and attendance status
Use this when you know the columns for Date, Role, and Status and need formula-driven weekly totals.
COUNTIFS can evaluate multiple criteria in one formula. To produce weekly totals, add a helper column that assigns each attendance row to a week start date, then reference that helper column together with role and status criteria.
The same approach works for any weekly reporting period by changing the start date in the summary cell.
Click any cell inside the attendance data range, press Ctrl+T, and make sure the columns are named Date, Role, Status, and WeekStart for easier formula creation.
In the first WeekStart data cell, enter =A2-WEEKDAY(A2,2)+1 and fill the formula down. This returns the Monday of each attendance week so the summary can group rows by week.
Click the sheet where you want the weekly totals and enter =COUNTIFS($D$2:$D$100,$G$1,$B$2:$B$100,"Doctor",$C$2:$C$100,"Attended"). Adjust the D, B, and C ranges to match your actual WeekStart, Role, and Status columns, and put the week start date in G1.
Change "Doctor" to "Nurse" or "Midwife" and change "Attended" to "Absent" as needed, or replace those hard-coded criteria with cell references so the formula can be dragged across a summary grid.
Create a PivotTable grouped by 7 days
Use this when you prefer an interactive summary without manually writing COUNTIFS formulas.
Count weekly attendance by job role with WPS Spreadsheets
Use WPS Spreadsheets to summarize weekly attendance by role with COUNTIFS formulas or a PivotTable. It opens your existing spreadsheet, preserves formulas, and makes it easy to count doctors, nurses, and midwives by week and attendance status.
- 1. Download WPS Office: Get WPS Office Free from the official WPS website, install it, and open your Excel attendance workbook.
- 2. Open the attendance file: Launch WPS Spreadsheets, click Open, and select the .xlsx file containing your attendance records.
- 3. Apply the weekly summary solution: Use the COUNTIFS formula steps or create a PivotTable as described above to count attendance by role and status for each week.
- 4. Save or share the report: Click Menu > Save As to keep the workbook in .xlsx format, or export the summary as a PDF for sharing with your team.
Frequently Asked Questions
What formula counts attendance between two dates and by role?
Use COUNTIFS with one date range criterion for greater than or equal to the week start date, another for less than the following week start date, plus separate criteria for Role and Status. Adjust the ranges to match your attendance columns.
How do I group daily attendance rows into weekly totals?
Add a WeekStart helper column using =A2-WEEKDAY(A2,2)+1 so every row is assigned to a Monday, or group a PivotTable date field by 7 days.
Why does my COUNTIFS attendance formula return 0 or #VALUE!?
Check that the Date column contains real dates instead of text, verify that role and status values exactly match the criteria, and ensure all range sizes in COUNTIFS are the same length.
Can I count both attended and absent by role in one summary?
Yes. Place Role and Attendance Status in separate columns, then use COUNTIFS for each role/status combination. In a PivotTable, drag Role to Columns and Attendance Status to Values.
Does this work for a July to June fiscal year?
Yes. Set the summary week start dates from July 1, 2023 through June 30, 2024, and use either the WeekStart helper column or a PivotTable grouped by week to keep the totals within that period.
