SOLVED

New Contributor

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

5 Replies
best response confirmed by yasirtherapist (New Contributor)
Solution

Re: How to count variables used in a sum formula?

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)

Re: How to count variables used in a sum formula?

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

Re: How to count variables used in a sum formula?

Ah super! Thanks so much. And so now if I were to extend that to say A1:A20, how could I go about doing that?

Re: How to count variables used in a sum formula?

That worked! Thank you!

Re: How to count variables used in a sum formula?

@yasirtherapist Fill or copy the formula down to row 20.