SOLVED

# Adding 2 numbers in a column with a comma in between

Highlighted
Occasional Contributor

# Adding 2 numbers in a column with a comma in between

This formula works to count more than one number in a cell separated by a comma in Google Sheets.

=COUNTA(SPLIT(JOIN(",",L10:L142),","))

1,2

3,4,5

See the Screenshot below.

Is there an equivalent one for excel?

Thanks for any and all assistance!

6 Replies
Highlighted

# RE: Adding 2 numbers in a column with a comma in between

Only if you install a small user-defined function like so: Function Eval(Ref As String) Application.Volatile Eval = Evaluate(Ref) End Function Then you can do: =eval(SUBSTITUTE(cell reference,",","+"))
Highlighted
Solution

# Re: Adding 2 numbers in a column with a comma in between

Hello @DonYTechGuy,

As a variant:

`=SUM(SUMPRODUCT(LEN(Cell_Range)),-SUMPRODUCT(LEN(SUBSTITUTE(Cell_Range,",",""))),COUNTA(Cell_Range))`
Highlighted

Highlighted

# Re: Adding 2 numbers in a column with a comma in between

Hello, @PReagan  Thank you so much for the very rapid reply. Your solution works just as I need it to! TY soon much. This has been a challenge for me for some time now! Thanks Again!!

Re:

`=SUM(SUMPRODUCT(LEN(Cell_Range)),-SUMPRODUCT(LEN(SUBSTITUTE(Cell_Range,",",""))),COUNTA(Cell_Range))`

Highlighted

You're welcome!

Highlighted

# Re: Adding 2 numbers in a column with a comma in between

From @DonYTechGuy  to   PReagan  FYI ... Just so other people will know from reading this thread ... This formula works in #GoogleSheets as well. It will also count letters as well as numbers.  Thanks Again for  the fantastic suggestion/solution!