Md. Abdullah Al Murad
VLOOKUP and COUNTIF are mostly used and powerful functions in MS Excel. VLOOKUP is used to find any specific data from any table and the COUNTIF function is for counting elements using conditions. With the combined formula of these two functions, we can search and count any values with conditions from any range. In this article, I will show various ways to use VLOOKUP with the COUNTIF function.
Download the Practice Workbook
3 Ways to Use VLOOKUP with COUNTIF Function
1. Count the Occurrences Using VLOOKUP and COUNTIF Functions
Let’s consider a dataset of student attendance. For this example, we just assume just a weekly attendance. Now we will count each student’s total attendances using the VLOOKUP and COUNTIF functions.
Step 1: Enter the formula in cell C19 and press Enter
=COUNTIF(F5:K14,VLOOKUP(C18,B5:C14,2,0))
Formula Explanation
- First look at the VLOOKUP function VLOOKUP(C18, B5:C14,2,0). C18 in this cell we will put the input data. Then B5:C14 is the range where we will search the data. 2 is for two columns (Name and ID) and the last 0 is for an exact match.
- If you want to learn more about this VLOOKUP function, visit this link
- After that COUNTIF function will search the ID which is got by the VLOOKUP function in the table range F5:K14.
- For more information about the COUNTIF function please visit this link
Step 2: Now enter any name in the C18 cell
2. Calculate Percentages Using VLOOKUP and COUNTIF functions
Let’s have a dataset of student marks for each course (like 6 courses). Now our concern is to find the average percentages of all the grades if there are at least 4 percentages of the grades. That’s means if any student has less than 4 percentage then we will simply return #NA! Otherwise, we will return the average percentages of the grades.
Step 1: Enter the formula in cell D18
=IF(COUNTIF(INDEX($C$4:$H$13,MATCH(D17,$B$4:$B$13,0),0),">0")<4,NA(),VLOOKUP(D17,$B$4:$I$13,8,0))
Formula Explanation
- Here along with VLOOKUP and COUNTIF, we are using IF, INDEX, MATCH functions also.
- MATCH(D17,$B$4:$B$13,0) this part is looking for the matched Names with our entered name. And 0 is used to get an exact match. To learn more about this function visit this link
- INDEX($C$4:$H$13, MATCH(D17,$B$4:$B$13,0),0),”>0″)<4 this part is checking if the matched named has no 0 percentages. For more information visit this link
- COUNTIF(INDEX($C$4:$H$13, MATCH(D17,$B$4:$B$13,0),0),”>0″)<4 using this we are counting if not zero cells have less than 4 percentages or not.
- Lastly using the IF function we are checking if the number of percentages is less than 4 or not. If the result is true then print #NA using NA() function. Otherwise, print the average marks with the help of the VLOOKUP function.
Step 2: Enter any name in cell D17 and press Enter
Step 3: Now enter another name that has less than 4 percentages
3. COUNTIF vs VLOOKUP for Determining If a Value Exists
In this section, we will try to find out how COUNTIF and VLOOKUP functions handle searching operations. To be specific we will see if the total count of any element is zero then what will be returned by COUNTIF and VLOOKUP functions.
Let’s assume we have a dataset of employees with their names and ID. There are repeated values in the table. Now, we will count the names and try to match them.
Step 1: Now enter the formula in cells F4 and J4. Copy the formula up to F6 and J6
For F4:
=COUNTIF($C$4:$C$13,E4)
For J4:
Step 2: Then enter the names in the input cells and see the output
As we can see if there is not match data then COUNTIF simply returns 0 on the other side VLOOKUP returns #NA error if there is no matched data.
VLOOKUP VS COUNTIF
| VLOOKUP | COUNTIF |
|---|---|
| The VLOOKUP function simply returns the search data from any range. | COUNTIF returns the total count with only one condition. |
| Returns #NA if the data is missing in the range. | Returns 0 if the data is missing in the range. |
| VLOOKUP formula is longer and takes more time to write. | COUNTIF is shorter than VLOOKUP and easy to use. |
Things to Remember
| Common Errors | When they show |
|---|---|
| #N/A in VLOOKUP | In practice, there are many reasons why you might see this error, including:
|
| Case-sensitive | COUNTIF is not case-sensitive. |
| #VALUE in COUNTIF | COUNTIF will return a #VALUE error when referencing another workbook that is closed. |
| Incorrect results of COUNTIF | COUNTIF returns incorrect results when used to match strings longer than 255 characters. |
Conclusion
These are some ways to use the VLOOKUP function with COUNTIF in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.
