Count Vacation Codes per Employee in Excel with INDEX and MATCH
Question details
Count how many times a vacation code appears across each employee’s daily leave data.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Employee names appear in a summary list that can be filtered by department, while daily leave codes are stored in rows on a DASHBOARD sheet.
- Observed behavior
- Each employee’s summary row should display the number of cells containing the vacation code K.
Check which DASHBOARD column contains employee names and which columns contain daily leave codes. The examples below assume names are in B5:B1000, codes are in C5:ZZ1000, and the employee to find is named in C6 on the summary sheet.
Count each employee’s vacation codes with INDEX, MATCH, and COUNTIF
Use this formula to find an employee’s row and count code K across its daily leave cells.
MATCH finds the employee’s position in the name range, INDEX returns the corresponding row of leave codes, and COUNTIF counts cells equal to K. The name range and code range must start and end on the same rows.
On the summary sheet, select the cell where you want the vacation count for the employee named in C6.
Enter =COUNTIF(INDEX(DASHBOARD!$C$5:$ZZ$1000,MATCH(C6,DASHBOARD!$B$5:$B$1000,0),0),"K") and press Enter.
Drag the result cell’s fill handle down alongside the employee names. Adjust the DASHBOARD ranges if your names or daily codes occupy different cells.

Count vacation codes with OFFSET and MATCH
Use OFFSET as an alternative when you want to define the width of the daily-code range explicitly.
Count Employee Vacation Codes in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use COUNTIF with INDEX and MATCH to count leave codes for each employee.
- 1. Open the workbook: In WPS Spreadsheets, open the workbook containing the DASHBOARD and employee summary sheets.
- 2. Check the ranges: On DASHBOARD, confirm that names are in B5:B1000 and daily leave codes are in C5:ZZ1000, or note the ranges you need to substitute.
- 3. Enter the count formula: On the summary sheet, select the result cell for the employee in C6 and enter =COUNTIF(INDEX(DASHBOARD!$C$5:$ZZ$1000,MATCH(C6,DASHBOARD!$B$5:$B$1000,0),0),"K").
- 4. Fill down and filter: Drag the fill handle down to calculate counts for other employees, then use the department filter on the summary list to display the employees you need.

Frequently Asked Questions
Why does the formula return #N/A?
MATCH cannot find an exact match for the value in C6. Check the employee name or ID and confirm that DASHBOARD!$B$5:$B$1000 includes it.
Can I count a different vacation code?
Yes. Replace "K" in the COUNTIF formula with the code you want, such as "V", or replace it with a cell reference containing the code.
Does the formula count only visible daily cells after filtering?
No. COUNTIF counts matching cells in the entire referenced employee row, including hidden cells. Filtering the summary employee list only changes which summary rows are displayed.
What happens if two employees have the same name?
MATCH finds the first exact match in the name range. Use a unique employee ID in both the summary sheet and the DASHBOARD lookup column to distinguish employees.
Can OFFSET use a dynamic width?
Yes. Replace the width argument, such as 366, with a cell reference or a formula that returns the number of daily-code columns. Make sure the resulting range covers only the cells you want to count.




