Trying to count occurrences

[roscoe]

I have a spreadsheet where I am tracking volunteers for an event. Everywhere I need a volunteer, I put a placeholder of "()" (no quotes). As I get a volunteer, I put the name between the parenthesis.

I need a way to count the number of occurrences of (). The problem is that some cells have more than one. I tried =COUNTIF($B$2:$Q$31,"*()*") but it appears to give me the number of cells that contain the string, not the total number of occurrences.

Ideas?

 

[Domenic]

Try...

=SUMPRODUCT(LEN($B$2:$Q$31)-LEN(SUBSTITUTE($B$2:$Q$31,"()","")))/2

 

[roscoe]

Perfect!!

...now can you explain how? I can't figure out what's going on.

 

[Domenic]

Let's say that A2 contains the following text value...

Some text (), some other text ()

LEN(A2) returns 32, which represents the number of characters within the text string.

SUBSTITUTE(A2,"()","") returns "Some text , some other text ", which represents the text value that remains after each instance of "()" has been removed.

LEN(SUBSTITUTE(A2,"()","")) returns 28, which represents the number of characters that remain after each instance of "()" has been removed.

LEN(A2)-LEN(SUBSTITUTE(A2,"()","")) returns 4, which represents the number of characters removed. In effect, it tells us the number of times "(" and ")" occurs.

The number 4 is then divided by 2, since there are two characters in "()", and returns 2, which represents the number of times "()" occurs.

In the SUMPRODUCT formula, this evaluation process takes place for each cell in the given range. To see how the formula is evaluated by Excel, select the cell containing the formula, and then select...

Formula > Evaluate Formula

...and click on 'Evaluate'.

 

[roscoe]

Wow...thanks for that detailed explanation. Very clever use of the substitution command. I'll definitely have to remember that one.