Apr 18 2021 08:24 AM
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 would be 3. Would like the COUNT of it as an output.
Wondering if there's a way of going about it.
Thank you
Yasir
Apr 18 2021 08:40 AM
SolutionLet'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)
Apr 18 2021 08:41 AM
Apr 18 2021 08:49 AM
Apr 18 2021 09:19 AM
@yasirtherapist Fill or copy the formula down to row 20.
Apr 18 2021 08:40 AM
SolutionLet'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)