SOLVED
Home

Adding 2 numbers in a column with a comma in between

%3CLINGO-SUB%20id%3D%22lingo-sub-1198390%22%20slang%3D%22en-US%22%3EAdding%202%20numbers%20in%20a%20column%20with%20a%20comma%20in%20between%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1198390%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20formula%20works%20to%20count%20more%20than%20one%20number%20in%20a%20cell%20separated%20by%20a%20comma%20in%20Google%20Sheets.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DCOUNTA(SPLIT(JOIN(%22%2C%22%2CL10%3AL142)%2C%22%2C%22))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E1%2C2%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E3%2C4%2C5%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESee%20the%20Screenshot%20below.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIs%20there%20an%20equivalent%20one%20for%20excel%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20for%20any%20and%20all%20assistance!%20%3Afolded_hands%3A%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-02-27%2009_24_24-OGTC%20Members%20Tennis%2003%2001%202020%20-%20Google%20Sheets.png%22%20style%3D%22width%3A%20397px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173894i5AF9A1344610C37C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222020-02-27%2009_24_24-OGTC%20Members%20Tennis%2003%2001%202020%20-%20Google%20Sheets.png%22%20alt%3D%222020-02-27%2009_24_24-OGTC%20Members%20Tennis%2003%2001%202020%20-%20Google%20Sheets.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1198390%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1198449%22%20slang%3D%22en-US%22%3ERE%3A%20Adding%202%20numbers%20in%20a%20column%20with%20a%20comma%20in%20between%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1198449%22%20slang%3D%22en-US%22%3EOnly%20if%20you%20install%20a%20small%20user-defined%20function%20like%20so%3A%20Function%20Eval(Ref%20As%20String)%20Application.Volatile%20Eval%20%3D%20Evaluate(Ref)%20End%20Function%20Then%20you%20can%20do%3A%20%3Deval(SUBSTITUTE(cell%20reference%2C%22%2C%22%2C%22%2B%22))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1198500%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%202%20numbers%20in%20a%20column%20with%20a%20comma%20in%20between%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1198500%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F569646%22%20target%3D%22_blank%22%3E%40DonYTechGuy%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20variant%3A%3C%2FP%3E%3CPRE%3E%3DSUM(SUMPRODUCT(LEN(%3CSTRONG%3ECell_Range%3C%2FSTRONG%3E))%2C-SUMPRODUCT(LEN(SUBSTITUTE(%3CSTRONG%3ECell_Range%3C%2FSTRONG%3E%2C%22%2C%22%2C%22%22)))%2CCOUNTA(%3CSTRONG%3ECell_Range%3C%2FSTRONG%3E))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1198536%22%20slang%3D%22en-US%22%3ERE%3A%20Adding%202%20numbers%20in%20a%20column%20with%20a%20comma%20in%20between%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1198536%22%20slang%3D%22en-US%22%3EI%20realised%20I%20misread%20your%20problem%20-%20ignore%20this.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1198577%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%202%20numbers%20in%20a%20column%20with%20a%20comma%20in%20between%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1198577%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%20Thank%20you%20so%20much%20for%20the%20very%20rapid%20reply.%20Your%20solution%20works%20just%20as%20I%20need%20it%20to!%20TY%20soon%20much.%20This%20has%20been%20a%20challenge%20for%20me%20for%20some%20time%20now!%20Thanks%20Again!!%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3ERe%3A%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DSUM(SUMPRODUCT(LEN(%3CSTRONG%3ECell_Range%3C%2FSTRONG%3E))%2C-SUMPRODUCT(LEN(SUBSTITUTE(%3CSTRONG%3ECell_Range%3C%2FSTRONG%3E%2C%22%2C%22%2C%22%22)))%2CCOUNTA(%3CSTRONG%3ECell_Range%3C%2FSTRONG%3E))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1198581%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%202%20numbers%20in%20a%20column%20with%20a%20comma%20in%20between%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1198581%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F569646%22%20target%3D%22_blank%22%3E%40DonYTechGuy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1198602%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%202%20numbers%20in%20a%20column%20with%20a%20comma%20in%20between%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1198602%22%20slang%3D%22en-US%22%3E%3CP%20data-unlink%3D%22true%22%3EFrom%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F569646%22%20target%3D%22_blank%22%3E%40DonYTechGuy%3C%2FA%3E%26nbsp%3B%20to%26nbsp%3B%3CSPAN%3E%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3EPReagan%3C%2FA%3E%26nbsp%3B%26nbsp%3BFYI%20...%20Just%20so%20other%20people%20will%20know%20from%20reading%20this%20thread%20...%20This%20formula%20works%20in%20%23GoogleSheets%20as%20well.%20It%20will%20also%20count%20letters%20as%20well%20as%20numbers.%26nbsp%3B%20Thanks%20Again%3CSPAN%3E%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3EPReagan%3C%2FA%3E%26nbsp%3B%3C%2FSPAN%3E%26nbsp%3Bfor%26nbsp%3B%26nbsp%3Bthe%20fantastic%20suggestion%2Fsolution!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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!  

 

 

2020-02-27 09_24_24-OGTC Members Tennis 03 01 2020 - Google Sheets.png 

 

6 Replies
Highlighted
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

Hello @DonYTechGuy,

 

As a variant:

=SUM(SUMPRODUCT(LEN(Cell_Range)),-SUMPRODUCT(LEN(SUBSTITUTE(Cell_Range,",",""))),COUNTA(Cell_Range))
Highlighted
I realised I misread your problem - ignore this.
Highlighted

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
Highlighted

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!