Counting Asterisks in a Column

Written by Allen Wyatt (last updated April 25, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


Steven uses Excel for a personalized movie database. In one column he has the rating he's given to each movie on a one-to-four scale, defined by using one, two, three, or four asterisks. He wants to add info showing how many '*' movies he has, how many '**' movies he has, etc. Steven has figured out how to count the number of '*' movies, but when he tries to count how many '**' movies are in the column the figure is wrong.

There are multiple ways that this can be handled, but first a comment about the choice of asterisks for a purpose such as this: In the grand scheme of things that is Excel, the asterisk has many purposes. It is used most often in formulas as the multiplication symbol and, almost as often, as a wildcard symbol in many formula arguments. For this reason, it is not a particularly good idea to use the asterisk for other purposes, particularly for items that you want to count, as with the movie ratings. It may be better, in this case, to simply use a number 1 through 4 for the ratings, because the numbers are very easy to work with and are unambiguous in their usage.

If you must use asterisks, then there are several ways you can put together a formula to do the counts. The SUMPRODUCT function will do the work nicely. Assuming the asterisks are in column C, you could use the following:

=SUMPRODUCT(--(C:C="*"))
=SUMPRODUCT(--(C:C="**"))
=SUMPRODUCT(--(C:C="***"))
=SUMPRODUCT(--(C:C="****"))

Note the use of the two minus signs in each of these formulas. This usage is most accurately referred to as a "double unary" (nerdy, right?) and is used to force True/False results to numeric equivalents (1/0). This is necessary because a formula like C:C="*" returns either True or False, and SUMPRODUCT requires numeric values. Without the forced conversion of the double unary, the SUMPRODUCT function would return 0 every time.

You could also use SUMPRODUCT a bit differently to simply check the length of whatever is in column C. This approach works well if C contains just asterisks, but will also work if you use something different than asterisks:

=SUMPRODUCT(--(LEN(C:C)=1))
=SUMPRODUCT(--(LEN(C:C)=2))
=SUMPRODUCT(--(LEN(C:C)=3))
=SUMPRODUCT(--(LEN(C:C)=4))

You can also use the straight SUM function, but the following formulas must be entered using Ctrl+Shift+Enter. (They are array formulas.)

=SUM(IF(C:C="*",1,0))
=SUM(IF(C:C="**",1,0))
=SUM(IF(C:C="***",1,0))
=SUM(IF(C:C="****",1,0))

Note that the examples so far use summing functions, specifically SUMPRODUCT and SUM. These functions don't have the problem mentioned earlier of misunderstanding the asterisk. You do run into the problem when using counting functions, though. For instance, the following will not give the desired results:

=COUNTIF(C:C,"*")

The asterisk functions as a wildcard character, matching anything in a cell. Thus, you end up with a count of all cells in column C that contain anything. You can specify that you want the asterisk treated as a literal character (instead of as a wildcard) by preceding it with a tilde, in this manner:

=COUNTIF(C:C,"~*")

When it comes to two asterisks, you might think that this will work:

=COUNTIF(C:C,"~**")

It won't; Excel interprets this as "a single literal asterisk followed by anything." In other words, the first asterisk is literal and the second is still a wildcard. It is each of the asterisks which must be preceded by tildes, in this manner:

=COUNTIF(C:C,"~*")
=COUNTIF(C:C,"~*~*")
=COUNTIF(C:C,"~*~*~*")
=COUNTIF(C:C,"~*~*~*~*")

Finally, if you want to skip using formulas all together, you could create a PivotTable that references the rating column for your movies. If you use that column as a row in the PivotTable and change the aggregation method so that it does a count of contents of the cells in the column, you can get a nice summary of how many of each type of rating you've awarded your movies.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12849) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Centering a Paragraph with the Keyboard

Need a quick shortcut that you can use to center your paragraph between the margins? The answer is here.

Discover More

Changing Text Orientation

Word allows you to change the orientation of text contained within certain objects, such as AutoShapes, text boxes, and ...

Discover More

Using Copy and Paste for Formatting

Want to copy formatting from one cell and paste it into another cell? It's easy to do if you use the Paste Special ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Removing Duplicates at a Reduced Precision Level

The precision of numeric values you display in a worksheet can be less than what is actually maintained by Excel. This ...

Discover More

Counting Only Money Winners

If a series of cells contain the amount of money won by individuals, you may want to count the number of individuals who ...

Discover More

Only Showing the Maximum of Multiple Iterations

When you recalculate a worksheet, you can determine the maximum of a range of values. Over time, as those values change, ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 7?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.