Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com Sep 2 '12 at 8:46

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

2 Answers 2

up vote 6 down vote accepted

You can use the following:

{=SUM(LEN(A1:A7)-LEN(SUBSTITUTE(A1:A7,"""","")))}

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
1  
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)

=SUMPRODUCT(LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6,"x","")))

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

 
discard

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.