Function Problems

Count Vacation Codes per Employee in Excel with INDEX and MATCH

Maira MehtabMaira Mehtab Nov 14, 2026 869 views

Question details

Count how many times a vacation code appears across each employee’s daily leave data.

Count Vacation Codes per Employee in Excel with INDEX and MATCH
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

On the summary sheet, select the cell where you want the vacation count for the employee named in C6.

2
Enter the formula

Enter =COUNTIF(INDEX(DASHBOARD!$C$5:$ZZ$1000,MATCH(C6,DASHBOARD!$B$5:$B$1000,0),0),"K") and press Enter.

3
Fill the formula down

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 each employee’s vacation codes with INDEX, MATCH, and COUNTIF
Filtering and matching: A department filter on the summary list changes which employee rows are visible, but the formula still counts all matching code cells in the employee’s DASHBOARD row. MATCH returns the first exact name match, so use unique employee names or IDs.
Count leave codes in WPS Spreadsheets

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. 1. Open the workbook: In WPS Spreadsheets, open the workbook containing the DASHBOARD and employee summary sheets.
  2. 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. 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. 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.
Count a vacation code across each employee’s daily leave rowOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and filtering toolsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

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.