Combine VLOOKUP and COUNTIF to Count the Occurrences in Excel

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

1. 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.
2. After that, in the criteria argument of the COUNTIF, you need to enter the VLOOKUP function.
3. In the VLOOKUP, refer to the employee’s name (Brock) for the lookup_value argument.
4. Next, in the table array argument, refer to the employee’s name data, which you have in the range A1:B11.
5. From here, in the col_index_num, and type 2 in the [range_lookup], enter 0 to get the exact match.
6. 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.