Counting occurences of a character in an excel spreadsheet

Question

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.

Answer 1

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

Answer 2

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.