What if your google sheet data is a list of names in a certain column or different columns and you are required to establish the number of times certain unique names are appearing. I will show in this tutorial a simplified way to solve this problem in google sheets using the COUNTIF function. The count if the function is used where a specified criterion is single. In this function, the IF portion of the function gives the criteria for the data to be counted while the COUNT portion function gives the total number of cells meeting the specified criteria.
The use of COUNTIF function follows a set of rules depending on whether the data range contains numbers or text values so as to specify the criterion in the COUNTIF function. For a data range containing numbers, comparison operators such as greater than (>), Less than (<), not equal to(<>), greater than equal to (>=) are used to express and check the number criterion which must be enclosed in double quotation marks such as “>= 25”.In expressions where cell references and comparison operators are applied, ampersand (&) must be used to join the reference cells. For data ranges containing text values, double quotation marks are used to enclose text strings. For example in our case, text values such as “SPIDERMAN”, “Batman”, “IRONMAN”, “Scoffield” and “VIKING” were enclosed in quotation marks.
1. To get the number of times a certain word or text value appears in the data range, extract the unique names in which you want to count the number of times they appear in the data.
2. Enter the formula =UNIQUE(Select the cells in the column with unique names to be counted, ” * unique name”) Click ENTER. In my screenshot example, I have several unique names written in different formats. Inserting (*) makes it a special case hence the function will count all the unique names irrespective of format; ie, whether written in UPPERCASE or Lowercase.
3. In my screenshot, for Batman I entered the function =COUNTIF( A1: A20, ” SPIDERMAN”) Then clicked ENTER to get the result. Following the same process for other unique names will give a specific number of times each unique name has appeared in the google sheets as shown below.
