As a frequent Google Sheets user, you may come across instances where you need to check and count occurrences of values in your dataset to avoid repetitions and errors but unfortunately, you may not know the best way to go about this.
In this tutorial, we are going to discuss How To Check If A Value Is In A Range In Google Sheets.
How To Check if a Value is in a Range in Google Sheets?
The easiest way to go about this is the use of the COUNTIF function. This function works on both numerical and text value ranges.
The COUNTIF function operates by analyzing and searching across the selected cell range and returns the total count of occurrences of the selected value.
When this function is used, a value of one or more is returned to the display or result cell. The COUNTIF function takes the syntax below.
=COUNTIF(range, criterion).
Where;
- Range – This is the selected cell range where the COUNTIF function conducts its search based on the given criterion.
- Criterion- This is the search value that the COUNTIF function scans for occurrences in the range.
Checking the repetitions in Google Sheets can be done in two ways.
- Counting occurrences.
- Checking for a single value in a range.
Counting Occurrences.
This is simply the use of the COUNTIF function to find and return the number of occurrences a specific value has. In the sample worksheet below, we have a list of food items.
Some appear more frequently than others. So we would use the COUNTIF function to find the number of times “Corn” was repeated in the worksheet.
Step 1: In cell B2, enter the formula =COUNTIF(A2:A15, Corn)
Step 2: Hit the Enter key on your keyboard and a value of 3 is returned. This implies that corn was repeated three times.
Checking for a Single Value in a Range.
Here, we want to check whether “Tomatoes” is available on the worksheet or not. This is done with the use of the IF and COUNTIF function.
Step 1: Enter the formula =IF(COUNTIF(A2:A15, B2)=1, “Present”, “Absent”).
Step 2: Hit the Enter key and a ” Present” statement is returned to the cell.
The IF function returned “Present” because the search key was present in the selected range. This formula doesn’t count the number of occurrences. It only signifies if the value is included in the range or not.
Final Thoughts.
The COUNTIF function is a very quick way to do this.
If you didn’t know how to use this function, ensure you learn and practice it because it would come in handy for your day to day spreadsheet tasks and operations.
Now you know How To Check If A Value Is In A Range In Google Sheets. I hope you found this short tutorial helpful. Enjoy.
