Count number of amounts in one formula in one cell

gillic

New Member
Joined
Nov 26, 2017
Messages
1
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
Joined
Nov 12, 2017
Messages
107
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
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,822
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
Joined
Nov 12, 2017
Messages
107

ADVERTISEMENT

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
Joined
Aug 18, 2015
Messages
10,822
That's exactly why! Glad you posted the VBA equivalent though.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Try this...
=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"+",""),"-",""))+1
 

Watch MrExcel Video

Forum statistics

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