How to count cells with specific text in Google Sheets?

Here’s a question from Merlin:

I work for a food delivery service need to do some analysis and validation on a large data set that requires me to count all deliveries that were not timely delivered during 2023. Each delivery is represented by a single row in our database. My question is how can i easily show the number of those late deliveries in a table and chart?

Find number of text occurrences in Google Sheets

Use the COUNTIF function to get the number of occurrences of a string in Google Sheets range of cells across one or multiple columns):

=COUNTIF(cell_range, criteria)

Step #1: Setup your spreadsheet

  • First off create your Google Sheets spreadsheet and import your data into it.
  • In our case this will be the simple list below:
  • Create a small table showing the different statuses that you would like to track. In our case the table name is Analysis by Status – shown in the section below.

Step #2: Count value occurrences

  • Then type our formula in the corresponding cell and autofill for the rest of the table rows.

Note the usage of absolute references (for example $B$2) to ensure that your column range stays fixed. You could as well use a named range here.

  • The Analysis by Status table displays a count of course occurrences by status.

Step #3: Create a simple chart

  • Highlight your table. In our case, that would be Analysis by Status
  • Hit Insert then select Chart.
  • Google Sheets will propose a simple chart which format fits your data. In our case it is a pie chart.
  • You can easily use the Chart Editor pane to change the chart type (to a line, scatter, bar chart etc’) as well as customize the colors, legends, labels as needed.
  • Here we go: