Forum Discussion
Solved
How to count variables used in a sum formula?
Hi, I'd like to COUNT all the variables used in the SUM formula. For e.g. =1000+2000+3000 (used as a formula in a cell) I'd like to count the variables in the formula - in the above e.g. it ...
Let's say you have a formula in A1 of the form =value1+value2+...
The number of values that contribute to the result is
=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(FORMULATEXT(A1),"+",""))+1
This won't work for a formula of the form =SUM(1000,2000,3000) or =SUM(A1:A3)
If it's just addition and not any other arithmetic operations or more complex formulas than your example, then you could try:
=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(FORMULATEXT(A1),"+",""))+1
=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(FORMULATEXT(A1),"+",""))+1
That worked! Thank you!