Count cells containing specific text with Google Sheets

Below is a simple formula to count the number of cells within a specific range that contain specific text. In this post we will also be discuss counting cells containing text, not just exact matches, and then how to filter the data.

=COUNTIF(A:A,"Specific Text")

This formula will count all the instances “Specific Text” is found within column A.

Formula Explained

Functions Used

  • COUNTIF

With the above formula COUNTIF will return a numerical value for all cells that exactly match your specified text.

Expanding Our Formula

The above formula is simple and effective to count cells containing specific text. Let’s assume we want a formula to count cells containing the text, not an exact match but a partial match.

Practice Sheet Scenario

You are the head of business development for Racer Inc. an organization that manages city marathons. Your boss wants to know which states in the US have 1 or fewer marathons a year. You have been given a list of all North American marathons. The source of the data can be found here. You quickly realize that the the marathon location data doesn’t separate the city and state data into separate columns.

Copy our practice sheet to follow along as we develop our formulas.

Formula Goal

  1. Scalable formula to count number of marathons for all 50 states.
  2. Formula to count cells that contain certain text, but aren’t exact matches.
  3. Filter list to display only states with less than 2 marathons a year.
  4. Autofill columns with formulas.
Count cells containing text and filter cells with Google Sheets

Additional Functions

  • COUNTIF
  • FILTER
  • CONCATENATE

Expanding Our Formula

We will need 2 formulas, one to count the number of marathons per state and another formula to filter the data to display only states with fewer than 2 marathons a year. You will notice in the linked data that the city and state are in the same cell so we will need to write a formula to count cells containing the state name and not an exact match.

Formula To Count Cells Containing Text

=COUNTIF('Data Source'!B:B,"*Alabama*")

By adding stars around our text it will now count cells containing our specific text without having to be an exact match. However, this is not a scalable formula as you would need to write out each state. Also note that we are counting cells in a different tab; that’s why you see 'Data Source'!.

=COUNTIF('Data Source'!B:B,"*"&A2&"*")

If we make a reference to cell A2 which contains the state’s name we will be able to use the autofill feature rather than writing out each state’s name. We will need to put the * in quotations and we will use & to combine all the information. The & operation works in a similar way as the CONCATENATE function.

Alternative Formula

=COUNTIF('Data Source'!B:B,CONCATENATE("*",A2,"*"))

Now that we are able to autofill our formula down the entire “Number of Marathons” column, we will need to write a formula to filter the states that have fewer than 2 marathons a year.

Filtering Cells

=FILTER(A:A,B:B<2)

In column C we are now able to use the FILTER function. In the formula above we are filtering all of column A which contains all 50 states. In order to filter, we set condition1 to filter data in column B that is less than 2. This will return the names of all the states that have 1 or fewer marathons a year. The filter formula requires the ranges to be identical in size to filter conditions from corresponding ranges.

You now have a simple list to hand to your boss.

Importing Data

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_marathon_races_in_North_America","table",3)

Bonus Tip: We copied the data so our practice sheet could always be used. But if you copy the above formula into a new tab in your practice sheet you can import the latest version of the data source used in the practice sheet.

The function IMPORTHTML is a powerful function that allows you to pull data from websites. You can extract data that is contained within a table or list HTML tag. The number 3 at the end of this function is telling our formula to pull the data from the third instance of an open table tag (<table>) within the linked Wikipedia page.

Leave a Comment