Skip to content

7. Sheets: Use COUNTIF to Generate Numeric Data with Text Values

Playback speed:

In a previous video, you used the count quick reference to find how many of  each size of shirt were sold.

But this tool only works to count numeric data.

What if you want to count  cells that contain text values?

For example, you might want to count the number of times a specific name or product  appears in a range or sheet.

In this video, you will use the  COUNTIF function to count cells with specific text values, then  create a table to summarize them in your sheet.

Return to the Filter Views sheet .

Choose a range of cells containing terms to  count in your data, such as shirt designs.

Beneath the data, type the design names.

Then, add the COUNTIF function to the next column.

Type an equals sign, then COUNTIF.

Select the COUNTIF  function from the menu.

Add your range, or list of options, to include.

In this example, it’s the shirt designs.

Hold down the shift key, then click on the cells containing the shirt  one designs through the shirt five designs...

Or, use your mouse to select  all the cells in that range.

Type a comma and quotation marks.

The next part of the function is the  criterion, or the condition to be met.

In this example, it’s the  name of the shirt design.

Then close the quotes and parentheses.

Spelling, punctuation, and  spacing affect functions, so be sure to write the named value  exactly as it appears in the sheet.

Press enter to complete the function.

Nice work!

The function calculates how many times the  t-shirt design appears in the range you selected.

Repeat the steps to count the number of designs  purchased for the rest of the shirt numbers.

Then, use the SUM function to calculate the total.

Now, copy the table...

And paste it into your summary sheet.

Oops! Your calculated amounts didn’t transfer!

When you copied the table, you  copied the functions, not the values, and your functions reference  cells in the wrong sheet.

Undo your paste.

This time, paste the values only.

Give your table a title and update the  formatting to match the other table in the sheet.

Now you can see all the possible shirt designs  and the amount of each design purchased.

Next, create another chart to represent this data.

Choose a pie chart.

This shows a small amount of data  that represents parts of a whole.

Name your chart...

...and adjust the formatting of the labels.

Change the colors, if you’d like.

Then, move the chart into its own  sheet.

Rename it...

...and assign it a color...

...and protect it.

Nice work!

Your pie chart shows the designs of each  shirt purchased as a percentage of the whole, or the total number of shirts sold.

Now it’s easy to see which  designs were more popular.

You might use this data to decide whether  you want to continue offering all designs or offer a certain design  at a discount, for example.

Follow the same steps to create a table and chart for the styles of shirts  that were sold, if you like.


  1. Use the COUNTIF function to count the number of shirts of each design that were sold.
  2. Format the calculations into a table.
  3. Insert a pie chart to visually represent the data.
  4. Add the chart to its own sheet and protect it.
  5. Repeat the same steps for shirt styles.