Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Feel silly asking this question, but I need some help!

  1. I want to count how many * appear in a range of 5 cells.
  2. The cells will range from 0-7 * each.

I was thinking of using the =COUNTIF(A2:A:6,"*") function, but if the cell has more than one *, it doesn't work. Any idea what function I should use or how I can modify my COUNTIF function to make it work?

Thanks in advance.

share|improve this question

migrated from Sep 2 '12 at 8:46

This question came from our site for professional and enthusiast programmers.

up vote 6 down vote accepted

You can use the following:


This counts the number of " within A1:A7.

Make sure to make this an array operator or it will not work correctly (control + shift + enter).

share|improve this answer
Ah! you beat me to it enderland +1.....I used SUMPRODUCT to avoid "array entry" – barry houdini Aug 31 '12 at 22:54

You can use this formula to count the number of "x"s in a range (including multiple occurrences in a single cell)


I'm not clear what character you are counting but replace x in the above with relevant character or you can replace "x" with CHAR(99) or similar if you know the number.

The above formula replaces your character with nothing then measures the cell length and compares it against the original cell length, obviously the difference is the number of instances of your character in each cell - SUMPRODUCT adds up the totals for all cells giving you a total for the range.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.