# Count number of amounts in one formula in one cell

#### gillic

##### New Member
Hi!
Does anyone know if there is a formula for counting the number of amounts between +/-/* in one formula in one cell? If I have a cell with a long, but simple calculation as:
=199+25+989+52,5-3849+2658+6598-1,22 (and hundreds more)
I'd like in the next cell to have the number of sums, here it would be 8.

Probably a dumb question, but would still be very useful to me if someone could help

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Dim Me as xlNoob

##### Board Regular
Hi gillic welcome to the forum.

Here is a user defined function that will count the number of operands in the target formula.

Code:
``````Private Function countOPERANDS(ByVal rngSUM As Range) As Long
Dim strSUM As String

strSUM = rngSUM.Formula
strSUM = Replace(strSUM, "+", "|")
strSUM = Replace(strSUM, "-", "|")
strSUM = Replace(strSUM, "*", "|")
strSUM = Replace(strSUM, "/", "|")
countOPERANDS = Len(strSUM) - Len(Replace(strSUM, "|", "")) + 1
End Function``````

To use a user defined function:
1. From Excel, open the visual basic editor with Alt + F11.
2. From the menu bar, select Insert > Module.
3. Paste the code into the new module.
4. You may now close the visual basic editor.

The function can now be used just like any other functions in that workbook by typing "=countOPERANDS(A1)" where A1 is the cell in which you want to count the number of operands in the formula.

Last edited:

#### JLGWhiz

##### Well-known Member
Hi!
Does anyone know if there is a formula for counting the number of amounts between +/-/* in one formula in one cell? If I have a cell with a long, but simple calculation as:
=199+25+989+52,5-3849+2658+6598-1,22 (and hundreds more)
I'd like in the next cell to have the number of sums, here it would be 8.

Probably a dumb question, but would still be very useful to me if someone could help
If you mean an Excel pre-defined function, then the answer is no. It might could be done with vba or vbs by looping and counting the ASCII charaters for the math operands. But I am not going to go there.

Looks like @Dim Me as xlNoob has already got the solution you need. Forgot that method.

Last edited:

#### Eric W

##### MrExcel MVP
Welcome to the board.

If all you have are {+,-,*,/} , and you have Excel 2013 or newer, then this might work for you:

=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"+",""),"-",""),"*",""),"/",""))+1

FORMULATEXT came in 2013, if you have something older, you might need VBA to look at it.

#### Dim Me as xlNoob

##### Board Regular

Welcome to the board.

If all you have are {+,-,*,/} , and you have Excel 2013 or newer, then this might work for you:

=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"+",""),"-",""),"*",""),"/",""))+1

FORMULATEXT came in 2013, if you have something older, you might need VBA to look at it.

I was scratching my head trying to figure out how to grab the formula from a cell as a string instead of the value. I'm using 2010 so that explains why!

#### Eric W

##### MrExcel MVP
That's exactly why! Glad you posted the VBA equivalent though.

#### FDibbins

##### Well-known Member
Try this...
=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"+",""),"-",""))+1

Replies
4
Views
179
Replies
3
Views
120
Replies
7
Views
83
Replies
3
Views
205
Replies
7
Views
121

1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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

### Which adblocker are you using?

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

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