Feb 27 2020 06:29 AM
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! :folded_hands:
Feb 27 2020 06:49 AM
Feb 27 2020 07:14 AM
SolutionHello @DonYTechGuy,
As a variant:
=SUM(SUMPRODUCT(LEN(Cell_Range)),-SUMPRODUCT(LEN(SUBSTITUTE(Cell_Range,",",""))),COUNTA(Cell_Range))
Feb 27 2020 07:24 AM
Feb 27 2020 07:41 AM
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))
Feb 27 2020 07:43 AM
Feb 27 2020 07:53 AM
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!
Feb 27 2020 07:14 AM
SolutionHello @DonYTechGuy,
As a variant:
=SUM(SUMPRODUCT(LEN(Cell_Range)),-SUMPRODUCT(LEN(SUBSTITUTE(Cell_Range,",",""))),COUNTA(Cell_Range))