Use COUNTIF to Generate Numeric Data with Text Values

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 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.

The "COUNTIF function" counts the number of times a specific value appears in a range of cells.

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.

The function calculates how many times a 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 function 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 tables 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.

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.

Now, it's your turn: use the COUNTIF function to count the number of shirts of each design that were sold, format the calculations into a table, insert a pie chart to visually represent the data, add the chart to its own sheet and protect it, and repeat the same steps for shirt styles.