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 a 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 from our data. 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 D2:J11. It is the range from where we need to count the IDs.
- After that, in the criteria argument of the COUNTIF, you need to enter the VLOOKUP function.
- In the VLOOKUP, refer to the employee’s name (Brock) for the lookup_value argument.
- Next, in the table array argument, refer to the employee’s name data, which you have in the range A1:B11.
- From here, in the col_index_num, and type 2 in the [range_lookup], enter 0 to get the 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 for the employee Brock for his 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 values, it finds that value in column A and then returns the ID from the corresponding cell from 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 the VLOOKUP to count it from the daywise shift data. In the 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
- Use of COUNTIF and VLOOKUP is dependent 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 returns an error, COUNTIF won’t work correctly to return the count, so you need to verify the data before writing the formula.
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.