Adding multiple numbers

[Real RFTC]

I can't figure out a simple way to add these numbers I plug in for each day to get my total amount for the week. auto sum and selecting "B3-G3" doesn't work... There has to be a way so I don't have to use a calculator and add everything up to get my total....

 

[Zot]

I have not tried but this was mentioned before and also a site talk about it

How to add numbers in a cell that are separated by commas

Hi, I'm wondering: If I have the following list of numbers contained in a cell: 1, 5, 3, 11, How can I get excel to add these numbers and put the answer in cell C9. Thanks :)

www.mrexcel.com

How to sum numbers with commas in a single Excel cell?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

For me, it is easier to use UDF

Book1
	A	B	C	D	E	F
1	30, 150	148, 99, 30	45, 130, 53	82, 54	96, 56, 82	14, 99, 58, 30
2
3	1256
Sheet1

Cell Formulas
Range	Formula
A3	A3	=CommaAdd(A1:F1)

Function CommaAdd(rng As Range) As Long

Dim s As String
Dim e As Variant, A As Variant
Dim cell As Range

For Each cell In rng
    s = s & "," & Trim(cell)
Next
A = Split(s, ",")
For Each e In A
    If Not e = "" Then CommaAdd = CommaAdd + Trim(e)
Next

End Function

 

[PeteWright]

If you don't want VBA, then use this formula at the end of the week (column G)

=SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(A2;",";REPT(" ";99));IF(SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2;",";""))+1)=1;1;(SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2;",";""))+1)-1)*99);99)))))+SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(B2;",";REPT(" ";99));IF(SEQUENCE(LEN(B2)-LEN(SUBSTITUTE(B2;",";""))+1)=1;1;(SEQUENCE(LEN(B2)-LEN(SUBSTITUTE(B2;",";""))+1)-1)*99);99)))))+SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(C2;",";REPT(" ";99));IF(SEQUENCE(LEN(C2)-LEN(SUBSTITUTE(C2;",";""))+1)=1;1;(SEQUENCE(LEN(C2)-LEN(SUBSTITUTE(C2;",";""))+1)-1)*99);99)))))+SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(D2;",";REPT(" ";99));IF(SEQUENCE(LEN(D2)-LEN(SUBSTITUTE(D2;",";""))+1)=1;1;(SEQUENCE(LEN(D2)-LEN(SUBSTITUTE(D2;",";""))+1)-1)*99);99)))))+SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(E2;",";REPT(" ";99));IF(SEQUENCE(LEN(E2)-LEN(SUBSTITUTE(E2;",";""))+1)=1;1;(SEQUENCE(LEN(E2)-LEN(SUBSTITUTE(E2;",";""))+1)-1)*99);99)))))+SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(F2;",";REPT(" ";99));IF(SEQUENCE(LEN(F2)-LEN(SUBSTITUTE(F2;",";""))+1)=1;1;(SEQUENCE(LEN(F2)-LEN(SUBSTITUTE(F2;",";""))+1)-1)*99);99)))))

MrExcel.xlsx
	A	B	C	D	E	F	G
1	Mon	Tue	Wed	Thu	Fri	Sat	Total
2	30, 150	148, 99, 30	45, 130, 53	82, 54	96, 56, 82	14, 99, 58, 30	1256
Sheet1

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),IF(SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)=1,1,(SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)-1)*99),99)))))+SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),IF(SEQUENCE(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)=1,1,(SEQUENCE(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)-1)*99),99)))))+SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(C2,",",REPT(" ",99)),IF(SEQUENCE(LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1)=1,1,(SEQUENCE(LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1)-1)*99),99)))))+SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),IF(SEQUENCE(LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1)=1,1,(SEQUENCE(LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1)-1)*99),99)))))+SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(E2,",",REPT(" ",99)),IF(SEQUENCE(LEN(E2)-LEN(SUBSTITUTE(E2,",",""))+1)=1,1,(SEQUENCE(LEN(E2)-LEN(SUBSTITUTE(E2,",",""))+1)-1)*99),99)))))+SUMPRODUCT(--(1*(TRIM(MID(SUBSTITUTE(F2,",",REPT(" ",99)),IF(SEQUENCE(LEN(F2)-LEN(SUBSTITUTE(F2,",",""))+1)=1,1,(SEQUENCE(LEN(F2)-LEN(SUBSTITUTE(F2,",",""))+1)-1)*99),99)))))

 

[Rick Rothstein]

For me, it is easier to use UDF

Book1
	A	B	C	D	E	F
1	30, 150	148, 99, 30	45, 130, 53	82, 54	96, 56, 82	14, 99, 58, 30
2
3	1256
Sheet1

Cell Formulas
Range	Formula
A3	A3	=CommaAdd(A1:F1)

Function CommaAdd(rng As Range) As Long

Dim s As String
Dim e As Variant, A As Variant
Dim cell As Range

For Each cell In rng
    s = s & "," & Trim(cell)
Next
A = Split(s, ",")
For Each e In A
    If Not e = "" Then CommaAdd = CommaAdd + Trim(e)
Next

End Function

If the range being processed is always horizontal like your example showed, then your function can be written more compactly like this...

Function CommaAdd(R As Range) As Double
  CommaAdd = Evaluate(Join(Split(Join(Application.Index(R.Value, 1, 0), ","), ","), "+"))
End Function

 

[Zot]

If the range being processed is always horizontal like your example showed, then your function can be written more compactly like this...

Function CommaAdd(R As Range) As Double
  CommaAdd = Evaluate(Join(Split(Join(Application.Index(R.Value, 1, 0), ","), ","), "+"))
End Function

Thanks. Not aware of Join command and the way to implement it ?

 

[Fluff]

Another possible formula

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Mon	Tue	Wed	Thu	Fri	Sat	Total
2	30, 150	148, 99, 30	45, 130, 53	82, 54	96, 56, 82	14, 99, 58, 30	1256
Data

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(FILTERXML("<k><m>"&SUBSTITUTE(CONCATENATE(A2,",",B2,",",C2,",",D2,",",E2,",",F2),",","</m><m>")&"</m></k>","//m"))

 

[Real RFTC]

Another possible formula

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Mon	Tue	Wed	Thu	Fri	Sat	Total
2	30, 150	148, 99, 30	45, 130, 53	82, 54	96, 56, 82	14, 99, 58, 30	1256
Data

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(FILTERXML("<k><m>"&SUBSTITUTE(CONCATENATE(A2,",",B2,",",C2,",",D2,",",E2,",",F2),",","</m><m>")&"</m></k>","//m"))

This code was very simple and got my fix! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.