Counting occurences of a character in an excel spreadsheet

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?

-

migrated from stackoverflow.comSep 2 '12 at 8:46

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

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

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

-