Trying to count occurrences

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
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?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

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

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
Perfect!!

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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Let's say that A2 contains the following text value...

Code:
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

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
Wow...thanks for that detailed explanation. Very clever use of the substitution command. I'll definitely have to remember that one.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,076
Messages
5,599,627
Members
414,326
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top