Count the Number of Addends in a Cell

[dwgnome]

Looking for a formula that will count the number of numbers being added in a cell.

Example: =56+4+9+23+11+155+8222, the result should be 7.
=7+0.2+8+0+dog+3, the result should be 4.

If possible I want it to ignore zeroes and text.

Thanks

 

[dwgnome]

Neil,

Thanks for your corrected version, however it does not quite get the correct results if zeroes are entered. In fact for every zero that is included, the result is the total non-zeroes minus the count of zero entries.

=99+29+6+106+98+103+6 = 7 Correct
=99+29+6+106+98+103+0 = 5 Incorrect
=99+29+6+106+98+0+0 = 3 Incorrect


pgc01, your UDF and formula version works perfectly. Thank you.

If you get the chance can you explain why =GET.CELL(6,INDIRECT("RC[-1]",FALSE)) when placed in a cell (not in defined name) gives an error "That function is not valid", while the Defined Name accepts the same formula. I have the MOREFUNC add-in if that makes a difference. What does the 6 do in the formula?

 

[shg]

regex.Pattern = "(\=|\+)(?!0+(\+|$))\d+(\.\d+)?(?=(\+|$))"

pgc, would you be so kind as to explain the backreferences?

 

[pgc01]

Hi shg

Sorry I did not answer before but I was away.

In this case I don't use backreferences. I use 2 lookaheads.

The logic for the match is

(sign "=" or "+")(Number not zero)(with the "+" sign or the "end of string" ahead)

with the pattern

"(\=|\+)Number not zero(?=(\+|$))"

where the "(?=(\+|$))" is a lookahead meaning followed by (but don't match) a "+" sign or the end of string.

- Pattern for the "Number not zero"

The problem here is that I want to exclude the number 0 but to accept decimals (that can start with a 0) and so I cannot use simply "[1-9]\d*"

For

=3+0+0.01+4

I want to accept the 3, the 4 and the 0.01, just want to exclude the 0.

What I did is I accept any number with decimals except the case of just 0's

So "=000+0.2" has count 1

(?!0+(\+|$))\d+(\.\d+)?

refers to a location in the string where you
- don't have ahead just 0's followed by a "+" sign or the end of string,
- but you have any other number with decimals

Remark: considering that we are talking about excel formulas, the formula will never have a number that is just several zeros. If you write

=00+2

and confirm, the formula will be just

=0+2

so we really only have to exclude the case of 1 character "0" preceeded by a "=" or a "+" and followed by a "+" or the end of string. That's why I wrote that "+" sign in red in this part of the pattern, because that "+" will not be needed in this case.

 

[pgc01]

If you get the chance can you explain why =GET.CELL(6,INDIRECT("RC[-1]",FALSE)) when placed in a cell (not in defined name) gives an error "That function is not valid", while the Defined Name accepts the same formula. I have the MOREFUNC add-in if that makes a difference. What does the 6 do in the formula?

I'm glad it helped.

The GET.CELL() will not work in a cell because it's not a function, it's a XLM macro, you have to use it inside a name to get the result.

The 6 means you want to get the formula in the cell. There are other information that you can get out of a cell, for which you use other values in the first parameter. I don't have the list here but if you do a search here on the board you can get it.

I don't have the MOREFUNC add-in but it may have functions that will allow the formula to be simplified. You'll have to explore it.

Cheers

 

[dwgnome]

Thanks pgc