Challenge Scenario
In payroll processing and corporate human resources management, employee work logs must be checked against official company holidays to calculate holiday overtime pay rates.
By comparing shift dates against a list of official company holiday dates using the COUNTIF function, payroll administrators can automatically verify whether a shift occurred on a designated holiday.
As a Payroll Compensation Specialist, your tasks are:
- Holiday Match Count (Column C): Check if Shift Date (
B2) appears in the Official Holidays list in Column G ($G$2:$G$3) using COUNTIF($G$2:$G$3, B2).
- Holiday Pay Eligibility (Column D): If the match count is greater than 0 (
C2 > 0), assign "HOLIDAY"; otherwise assign "REGULAR".
- Total Logged Shifts (Cell B8): Count total recorded shifts using
COUNTA(A2:A5).
- Holiday Shifts Count (Cell C8): Count how many shifts earned the
"HOLIDAY" rate using COUNTIF.
Your Sheet Layout
Understanding how the holiday verification worksheet is structured:
- Shift Logs (Columns A & B): Shows employee names and logged work shift calendar dates.
- Holiday Verification (Columns C & D): Counts matches against the holiday calendar and assigns the compensation rate status.
- Official Holiday Calendar (Columns F & G): Lists company recognized holidays (e.g. New Year's Day, Labor Day).
- Payroll Audit Summary (Rows 7 & 8): Summarizes total shifts worked and counts shifts eligible for premium holiday rates.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B5 |
Employee, Shift Date |
Shift Logs |
Employee shift records |
Read-only work attendance records. |
F2:G3 |
Holiday Name, Date |
Holiday Calendar |
Official company holidays (2026-01-01, 2026-05-01) |
Holiday master schedule. Lock with $G$2:$G$3. |
C2:C5 |
Holiday Match |
Calculation |
=COUNTIF($G$2:$G$3, B2) |
Returns 1 if date is in holiday list, else 0. |
D2:D5 |
Pay Status |
Classification |
=IF(C2 > 0, "HOLIDAY", "REGULAR") |
Assigns premium holiday rate if match > 0. |
B8 |
Total Logged Shifts |
Summary |
=COUNTA(A2:A5) |
Counts total recorded shift entries (4). |
C8 |
Holiday Shifts |
Summary |
=COUNTIF(D2:D5, "HOLIDAY") |
Counts shifts qualifying for holiday pay (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column B (B2:B5), output "HOLIDAY" if date is in $D$2:$D$4 using COUNTIF, otherwise "REGULAR".
2
In cell B8, count total services using COUNTA(A2:A5).
3
In cell C8, count how many services are "HOLIDAY" using COUNTIF(B2:B5, "HOLIDAY").
4
In cell D8, count how many services are "REGULAR" using COUNTIF(B2:B5, "REGULAR").
Solution & Formula Breakdown
Let's examine how Excel compares shift dates against a calendar list:
1. Matching Against the Holiday Table with COUNTIF (C2:C5)
=COUNTIF($G$2:$G$3, B2)
The COUNTIF(range, criteria) function checks how many times the shift date in B2 appears in the holiday table $G$2:$G$3:
- Row 2 (2026-01-01, New Year): Found in holiday list → returns 1.
- Row 3 (2026-01-15, Regular Workday): Not in holiday list → returns 0.
- Row 4 (2026-05-01, Labor Day): Found in holiday list → returns 1.
- Row 5 (2026-05-02, Regular Workday): Not in holiday list → returns 0.
2. Assigning Holiday Pay Status with IF (D2:D5)
=IF(C2 > 0, "HOLIDAY", "REGULAR")
If the match count is greater than 0, output "HOLIDAY"; otherwise output "REGULAR".
3. Payroll Summary Rollups (B8 & C8)
- Total Logged Shifts (Cell B8):
=COUNTA(A2:A5) returns 4.
- Holiday Shifts (Cell C8):
=COUNTIF(D2:D5, "HOLIDAY") returns 2.
Using COUNTIF as a membership or lookup existence check is fast, clean, and avoids generating ugly #N/A errors!