How to Use VLOOKUP with COUNTIF Function in Google Sheets

We often need to get the result of a particular task in the fastest possible way. Like, if an employee of a company has a dataset containing records of all the workers like their attendance, average working hours, and much more, and if he wants to see all the records of a particular worker, he doesn’t have time to walk through the whole dataset and find them one by one. So what can he do? He can combine some amazing formulas like IF, INDEX, MATCH, VLOOKUP, COUNTIF and much more to get his desired result within the shortest possible time. In this article, we have demonstrated some examples on how to use VLOOKUP with COUNTIF function in Google Sheets.

A Sample of Practice Spreadsheet

You can download the spreadsheet used to demonstrate examples in this article.

4 Ideal Examples to Use VLOOKUP with COUNTIF Function in Google Sheets

We can use the VLOOKUP and COUNTIF functions in many ways. Here we have described 4 usual examples that are necessary from many perspectives.

Example 1. Finding Total Appearances of Attendees in a Session

Let’s assume, the following dataset is containing the records of 6 attendees’ appearances of a 5-day session on a particular topic. We want to calculate the total appearances of each attendee in that session using VLOOKUP and COUNTIF functions.

Steps:

• First, select Cell B15 and then apply the following formula below and press Enter–

• Here, we can see in Cell B15 that the total number of appearances of attendee “Annie” is 4.

• Now if I input “Annie” in a different cell within Cell range D6:H11, the total number of appearances will be changed. Like, if I input “Annie” in Cell F8 then see what happens!

• The total number of appearances of “Annie” is now 5.
• Now, simply drag down using the Fill Handle icon as shown in the circled portion.

• And you will get the total number of appearances of the other attendees as well.

Read More: Google Sheets Count Cells from Another Workbook with COUNTIF Function

Example 2. Counting Total Attendance

Suppose, in the following dataset, one-week attendance record of some students is given along with their ID number. What we want to know is the total attendance of a particular student within just a moment.

Steps:

• In the following dataset, select Cell H18 and apply the formula given below then press Enter–

• Here we can see what appears in Cell H18 is “0”. It’s because nothing is given in Cell H17 as input.

• Now, if you type down one of the students’ names in Cell H17, the total number of attendances of that student will appear in Cell H18. Like, if you type down “Annie” in Cell H17 the total number of attendances of “Annie” will appear in Cell H18 and that will be “4”.

Read More: [Fixed!] COUNTIF Function Is Not Working in Google Sheets

Similar Readings

• How to Execute Case Sensitive COUNTIF in Google Sheets
• Google Sheets Add Calculated Field for Pivot Table with COUNTIF
• How to Use COUNTIF Function with OR Logic in Google Sheets

Example 3. Confirming Match and Calculating Total Count

Presume, we have a dataset of some students with their ID. What we want to check is whether a student is on the dataset list or not and if he exists then what count of that particular student is present in the dataset?

Steps:

• In the following dataset, select Cell F5 then apply the following formula-

• As nothing is typed down yet in Cell E5, that’s why the match is shown as “#N/A”.
• Now apply the following formula in Cell G5 and then press Enter–

• The result is showing “0” as no input is given in Cell E5.
• Now type down any name from the list or outside the list in Cell E5 and it will show satisfactory results. Like if you type down “Annie” in Cell E5 then it will show “Annie” in Cell F5 and the total number of counts for “Annie” will be shown “4” in Cell F5 as the name “Annie” is on the list about 4 times.

• And if we input a random name that is not on the list, then the result will be as follows-

Read More: COUNTIF Function with “Not Equal to” Criterion in Google Sheets

Example 4. Calculating Percentage

Let’s assume, we have a dataset of grade sheets of some students. Now, if there are at least 3 percentages for each grade, our target is to get their average percentage. That means if any student has more or equal to 3 percentages, the result will be the average percentage. If any student has less than 3 percentages, then the result will return as “#N/A”. We will be using the combination of VLOOKUP, COUNTIF, IF, INDEX, MATCH and NA functions.

Steps:

• First, select Cell C18, apply the following formula and press Enter–

• What we can see is the result returned as “#N/A”. This is because no such entry is given in Cell C17.
• Now, suppose we want to know the average percentage result of a student named “Maria”. Simply type down “Maria” in Cell C17 and press Enter-

• We can see that the average percentage mark of “Maria” is “63%”. The result returned as an average percentage form as “Maria” has 4 percentages of 4 subjects which is obviously greater than 3.
• Now, we will type down “Eva” in Cell C17, and let’s see what happens-

• The result returned as “#N/A” because “Eva” has only percentages of 2 subjects which is less than 3.

Read More: COUNTIF with Greater than and Less than Criteria in Google Sheets

Conclusion

We can solve almost all the time-consuming tasks within the shortest possible time in Google Sheets or Excel. Using VLOOKUP and COUNTIF is just a small part of that. In this article, we have described 4 ideal examples on how you can use VLOOKUP with COUNTIF in Google Sheets. Hope this will help you with your task. Visit our site officewheel.com to see more related articles.

Related Articles

• How to Use COUNTIF for True Condition in Google Sheets
• Use COUNTIF If Cell Contains Specific Text in Google Sheets
• How to Use COUNTIF for Cells Not Equal to Text in Google Sheets
• Use COUNTIF Function to Count Checkbox in Google Sheets