Counting Asterisks in a Column

by Allen Wyatt
(last updated July 7, 2018)

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="*")*1)
=SUMPRODUCT(--(C:C="**")*1)
=SUMPRODUCT(--(C:C="***")*1)
=SUMPRODUCT(--(C:C="****")*1)
```

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

Right Aligning a Table Column with an Indent

Word allows you to get a bit fancy in formatting the alignment of your tables. In this tip, you discover how to enter ...

Discover More

Generating a Table of Authorities

With the entries for your table of authorities marked throughout your document, you are ready to actually generate the ...

Discover More

Automatically Formatting Graphics and Shapes

Want to change the graphics formatting defaults in Word? You can customize some of these defaults, saving yourself some time.

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Extracting a State and a ZIP Code

Excel is often used to process or edit data in some way. For example, you may have a bunch of addresses from which you ...

Discover More

Using Named Formulas Across Workbooks

You can use the naming capabilities of Excel to name both ranges and formulas. Accessing that named information in a ...

Discover More

Sometimes making sure that a reference in a formula doesn't get changed is not as simple as putting dollar signs in front ...

Discover More
Subscribe

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

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}] 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 nine more than 3?

2017-09-06 10:47:54

Neil

Thanks Micky, I'd never heard of the "N" function before.

2014-01-07 09:21:10

Michael (Micky) Avidan

In most cases (if not all) there is a third way:
=SUMPRODUCT(N(LEN(C:C)=1))
The function "N" converts TRUE into 1 and FALSE into 0.

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL

2014-01-06 10:44:52

Bryan

The article doesn't explain the purpose of the -- (double unary) and the *1 in the SUMPRODUCT formulas.

Since the comparisons result in a TRUE/FALSE result, and Excel can only sum numbers, you need to convert TRUE to 1 and FALSE to 0; this process is called coercion. You can coerce boolean values to numeric by using them in a numerical expression. The two easiest ways are the double unary, --, which multiplies the boolean by -1 twice, or by multiplying by 1. Each give you the same thing, but I personally find multiplying by 1 the most clear.

As has been mentioned in the comments, there's no reason to coerce the value twice; you need either the -- or *1, but not both.

2013-12-26 16:23:26

Don

One more thing...

If using the SUM(IF...) solution, the formula for row and column headers is

=SUM(IF(\$J:\$J=\$A2,IF(\$K:\$K=C\$1,1,0)))

2013-12-26 15:45:03

Don

I learned a lot from this. Thanks to you Alan, and everyone who contributed. Specifically, I had never learned to use SUMPRODUCT nor did I know how to use the tilde as an escape.

I like to put things in tables with labels. For these example, each row for the summary is labeled in Column A, i.e. A1=*, A2=**. The ratings for the movies are in Column J.

The SUMPRODUCT and SUM (Array) frmulas are very straightforward
=SUMPRODUCT(--(\$J:\$J=\$A2)*1)
=SUM(IF(\$J:\$J=\$A2,1,0))

I found that I could also use COUNTIF if I used a SUBSTITUTE function to replace each asterisk with tilde-asterisk ("~*")
=COUNTIF(\$J:\$J,SUBSTITUTE(\$A2,"*","~*"))

If I want to add category labels in Row 1 (e.g. Comedy, Noir, Silent, Musical), I can change the COUNTIF to a COUNTIFS and just add the information for the column label:
=SUMPRODUCT(--(\$J:\$J=\$A2)*--(\$K:\$K=C\$1))
=COUNTIFS(\$J:\$J,SUBSTITUTE(\$A2,"*","~*"),\$K:\$K,C\$1)

And that gets me to what I vote for as the best solution: Use a PivotTable, it's much simpler to do.

2013-12-23 11:29:51

Gerald Diamond

I am not sure why one needs both the -- and the *1 in the first version
SUMPRODUCT(--(C:C="*")*1)

Leaving out the *1 seems to work just as well in my version (2010)

Other variations that give the same result include

SUMPRODUCT(abs(C:C="*"))
SUMPRODUCT((C:C="*")^2)
and oddly
SUMPRODUCT((C:C="*")^1)

2013-12-23 10:59:15

Gerald Diamond

I found this instructive but have some questions. I am using 2010 and found that I could drop either the "--" or the "*1" and get the same results. Dropping both didn't seem to work. So I am not quite sure that I really understand the need for both or what they each do.
I did find that, in this example anyway, replacing the two with
abs(c:c="*")
also seemed to work.

2013-12-21 20:52:48

Peter Atherton

If I used the COUNTIF function I'd probably also include the REPT function

=COUNTIF(A:A,REPT("~*",ROWS(\$1:1)))

and copy down

2013-12-21 13:18:18

Juan

Excellent answer, thank you very much!

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.

Subscribe

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