You can nest VLOOKUP inside COUNTIF when the value you need to count isn’t the one you have. Put VLOOKUP in the criteria argument: =COUNTIF(E2:J11,VLOOKUP(B13,A1:B11,2,0)). VLOOKUP looks up the employee name in B13, returns their ID from the second column, and COUNTIF then counts how many times that ID appears across the shift grid.
So you can count by a name the user recognises while the data itself stores IDs. Two things to watch.
If VLOOKUP can’t find the name it returns #N/A, and COUNTIF passes that error straight through rather than returning 0 — wrap it in =IFNA(COUNTIF(...),0) so a missing name shows a count instead of an error.
And the lookup must return an exact match to what’s stored in the counted range: an ID stored as text in one place and a number in the other returns 0 with no warning. If the ID is already in your sheet, you don’t need VLOOKUP at all — =COUNTIF(E2:J11,B14) is enough.
In Excel, you can combine VLOOKUP with COUNTIF to count the occurrences of a value within your data. VLOOKUP will get the values you need to count, and then COUNTIF will count the occurrences of that value from the data.

In the above data, we need to count the number of shifts an employee has in the week. So, let’s get started.
Write a Formula with VLOOKUP and COUNTIF to Count
- First, enter the COUNTIF function, and in the range argument, refer to the data range E2:J11. It is the range from where we need to count the IDs.
- After that, in the criteria argument of COUNTIF, you need to enter the VLOOKUP function.
- In VLOOKUP, refer to the cell with the employee’s name (Brock in cell B13) for the lookup_value argument.
- Next, in the table_array argument, refer to the employee’s name and ID data, which you have in the range A1:B11.
- From here, type 2 in col_index_num, and in [range_lookup], enter 0 to get an exact match.
- In the end, close both functions by entering the closing parentheses and then hit Enter to get the result.

=COUNTIF(E2:J11,VLOOKUP(B13,A1:B11,2,0))
And when you hit Enter, it returns the count of the employee Brock’s shifts in the week.

To understand this formula where we have combined VLOOKUP and COUNTIF, we need to break down the formula into two parts and then see how both parts work.
VLOOKUP – When you use VLOOKUP and refer to the employee’s name for the lookup value, it finds that value in column A and then returns the ID from the corresponding cell in column B.

In the result, we have 7, which means the ID of the employee is 7. So now, from here, the work of COUNTIF starts.
COUNTIF – It takes the employee ID returned by VLOOKUP and counts it in the day-wise shift data. In COUNTIF, you already referred to the data, and now VLOOKUP has returned the ID, which you need to use as criteria to get the count from the data.
Notes
- Using COUNTIF and VLOOKUP together depends on the type of data that you have. In the example we have used above, VLOOKUP finds the value we used as criteria for COUNTIF.
- If VLOOKUP can’t find the name, it returns #N/A, and the whole formula returns #N/A too, so you need to verify the data before writing the formula (or use IFERROR, shown below).
Return 0 Instead of #N/A
When the name in B13 isn’t in the list, VLOOKUP returns #N/A, and so does the formula. Wrap it in IFERROR to show 0 instead:
=IFERROR(COUNTIF(E2:J11,VLOOKUP(B13,A1:B11,2,0)),0)
Just keep in mind that IFERROR also hides other mistakes, like a wrong range, so check the result once before you rely on it.
Use INDEX MATCH or XLOOKUP Instead of VLOOKUP
VLOOKUP only works when the IDs are to the right of the names. INDEX and MATCH work in any direction and give the same result:
=COUNTIF(E2:J11,INDEX(B2:B11,MATCH(B13,A2:A11,0)))
And in Excel 2021 and Microsoft 365, you can use XLOOKUP, which also lets you set your own value for a missing name in its fourth argument:
=COUNTIF(E2:J11,XLOOKUP(B13,A2:A11,B2:B11))
Count Shifts for All Employees
If you want the count for every employee in the list, you don’t need VLOOKUP at all. Put the following formula in a cell next to the first ID (row 2) and drag it down. It counts the ID from column B in the shift data, and the dollar signs keep the shift range fixed:
=COUNTIF($E$2:$J$11,B2)
Frequently Asked Questions
Why does my formula return 0?
VLOOKUP found the ID, but COUNTIF didn’t find it in the shift range. The most common reason is that the IDs are numbers in one place and text in the other (or have extra spaces), so they don’t match.
Can I count with more than one condition?
Yes. Use COUNTIFS with the VLOOKUP in one of its criteria, and add more range and criteria pairs for the other conditions. Each range must be the same size.
Does COUNTIF with VLOOKUP work in Google Sheets?
Yes. The same formula works in Google Sheets without any change.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.