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.
=countif($B$2:$B$8,D2)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:
