Excel Formulas to Count Shift Codes and Calculate Work Hours
Question details
The user needs formulas to count shift codes and calculate hours for each employee and shift type.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A work rota uses codes such as D1, D2, and D3 for shifts with different durations.
- Observed behavior
- The desired result is a count of each code and corresponding hour totals per employee and shift type.
Confirm the hours assigned to each shift code and identify the cells containing each employee's rota entries. The example below uses B3:D10 for shift entries and three codes; adjust the ranges to fit your rota.
Count shift codes and calculate hours with a lookup table
Use this method to see counts and hours for each shift type, plus a total for each employee.
This example assumes employees are listed in rows 3 through 10, with their shift codes in columns B through D. It uses D1, D2, and D3 as example codes.
Enter D1, D2, and D3 in N3:N5. Enter the corresponding shift lengths in hours in O3:O5.
Enter D1, D2, and D3 in F2:H2 for the count columns. Enter the same codes in the same order in I2:K2 for the hours columns.
In F3, enter =COUNTIF($B3:$D3,F$2). Drag the fill handle across to H3, then down through row 10.
In I3, enter =F3*VLOOKUP(I$2,$N$3:$O$5,2,FALSE). Drag the fill handle across to K3, then down through row 10.
In L3, enter =SUM(I3:K3) and fill down through L10 for each employee's total. To total D1 hours across employees, enter =SUM(I3:I10) in I11 and fill across to K11.
Calculate Shift Counts and Hours in WPS Spreadsheets
Open your rota in WPS Spreadsheets and use COUNTIF, VLOOKUP, and SUM to calculate shift counts and work hours.
- 1. Open the rota: Open WPS Spreadsheets and use File > Open to select your rota workbook.
- 2. Set up codes and hours: Enter shift codes in N3:N5 and their hours in O3:O5. Add matching code headers in F2:H2 and I2:K2.
- 3. Count and calculate: Enter =COUNTIF($B3:$D3,F$2) in F3 and fill across and down. Enter =F3*VLOOKUP(I$2,$N$3:$O$5,2,FALSE) in I3 and fill across and down.
- 4. Add totals: Enter =SUM(I3:K3) in L3 and fill down for employee totals. Enter =SUM(I3:I10) in I11 and fill across to total each shift type.
Frequently Asked Questions
How do I count one shift code in an employee's rota?
If the employee's codes are in B3:D3, enter =COUNTIF(B3:D3,"D1") to count D1. Change the range or code to match your sheet.
Why does VLOOKUP return #N/A for a shift code?
Check that the code in the hours header appears in the first column of the lookup table and that there are no unwanted spaces. For the example formula, verify I2 against N3:N5.
How can I total hours for one shift type across all employees?
Sum that shift's hours column. For example, enter =SUM(I3:I10) to total D1 hours when I3:I10 contains the employees' D1 hours.
What if my rota has more than three shift entries per employee?
Expand the COUNTIF range to include all rota columns. For example, use =COUNTIF($B3:$AF3,F$2) if the entries extend through column AF, and move the summary columns so they do not overlap the rota.
