Forum Discussion
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! 🙏
Hello DonYTechGuy,
As a variant:
=SUM(SUMPRODUCT(LEN(Cell_Range)),-SUMPRODUCT(LEN(SUBSTITUTE(Cell_Range,",",""))),COUNTA(Cell_Range))
6 Replies
Hello DonYTechGuy,
As a variant:
=SUM(SUMPRODUCT(LEN(Cell_Range)),-SUMPRODUCT(LEN(SUBSTITUTE(Cell_Range,",",""))),COUNTA(Cell_Range))
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))
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 PReagan for the fantastic suggestion/solution!
- 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,",","+"))
- I realised I misread your problem - ignore this.
