Add numbers that are part of a string in cells

[cyberdimitri]

Hi All,

I'm trying to add the numbers that are part of a string in a row of a table.

So, for the table below, I want some formulas in the cells A4:A9 that add all the numbers in the cells of row 1 that start with the same letter.

Please note that I need the cells that start with "AS" to be distinguished from the cells that start with "A".

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT((LEFT($A$1:$M$1,LEN(A4))=A4)*(IFERROR(MID($A$1:$M$1,LEN(A4)+1,100)+0,0)))

 

[6StringJazzer]

What version of Excel do you used? I suggest you update your profile to show it. The solution could be different for the newest version of Excel vs. older versions.

 

[JamesCanale]

A simple 365 solution.

MrExcelPlayground10.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	AS1	A11	D23	F41	Q1	A2	A44	A12	S12	Q11	AS21	D3	S11
2
3	1	11	23	41	1	2	44	12	12	11	21	3	11
4	AS	A	D	F	Q	A	A	A	S	Q	AS	D	S
5
6	A	69
7	AS	22
8	D	26
9	F	41
10	Q	12
11	S	23
Sheet16

Cell Formulas
Range	Formula
A3:M3	A3	=MAX(IFERROR(VALUE(RIGHT(A1,SEQUENCE(LEN(A1)))),0))
A4:M4	A4	=LEFT(A1,LEN(A1)-LEN(A3))
A6:A11	A6	=SORT(UNIQUE(TRANSPOSE(A4:M4)))
B6:B11	B6	=SUMIFS(A3:M3,A4:M4,A6#)
Dynamic array formulas.

 

[cyberdimitri]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT((LEFT($A$1:$M$1,LEN(A4))=A4)*(IFERROR(MID($A$1:$M$1,LEN(A4)+1,100)+0,0)))

Thanks, it worked! I also updated my account details, thanks for the suggestion!
I really appreciate your time and I may be taking advantage of your kindness by asking what the "100" in the formula does, not necessary for me to know, but I'd like to learn.

 

[cyberdimitri]

What version of Excel do you used? I suggest you update your profile to show it. The solution could be different for the newest version of Excel vs. older versions.

Hey, thanks for the suggestion, I use Office 365 on a Windows PC. I updated my profile to show this.

A simple 365 solution.

MrExcelPlayground10.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	AS1	A11	D23	F41	Q1	A2	A44	A12	S12	Q11	AS21	D3	S11
2
3	1	11	23	41	1	2	44	12	12	11	21	3	11
4	AS	A	D	F	Q	A	A	A	S	Q	AS	D	S
5
6	A	69
7	AS	22
8	D	26
9	F	41
10	Q	12
11	S	23
Sheet16

Cell Formulas
Range	Formula
A3:M3	A3	=MAX(IFERROR(VALUE(RIGHT(A1,SEQUENCE(LEN(A1)))),0))
A4:M4	A4	=LEFT(A1,LEN(A1)-LEN(A3))
A6:A11	A6	=SORT(UNIQUE(TRANSPOSE(A4:M4)))
B6:B11	B6	=SUMIFS(A3:M3,A4:M4,A6#)
Dynamic array formulas.

Hey James, Thanks for your answer! I didn't test it as Fluff's solution worked perfectly, but I do appreciate your time!

 

[Fluff]

The 100 is just an arbitrary number to get everything in the cell after the start point.
As you have xl365 you could also use the formula in A4 to get the letters & another option in C4 for the sums

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	AS1	A11	D23	F41	Q1	A2	A44	A12	S12	Q11	AS21	D3	S11
2
3
4	A	69	69
5	AS	22	22
6	D	26	26
7	F	41	41
8	Q	12	12
9	S	23	23
10
Data

Cell Formulas
Range	Formula
A4:A9	A4	=SORT(UNIQUE(TRANSPOSE(LEFT(A1:M1,1+ISERR(MID(A1:M1,2,1)+0)))))
B4:B9	B4	=SUMPRODUCT((LEFT($A$1:$M$1,LEN(A4))=A4)*(IFERROR(MID($A$1:$M$1,LEN(A4)+1,100)+0,0)))
C4:C9	C4	=SUM(IFERROR(FILTER(MID($A$1:$M$1,LEN(A4)+1,100),LEFT($A$1:$M$1,LEN(A4))=A4)+0,0))
Dynamic array formulas.

 

[Peter_SSs]

Another option to consider for the sums

22 07 19.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	AS1	A11	D23	F41	Q1	A2	A44	A12	S12	Q11	AS21	D3	S11
2
3
4	A	69
5	AS	22
6	D	26
7	F	41
8	Q	12
9	S	23
Sum

Cell Formulas
Range	Formula
B4:B9	B4	=SUM(IFERROR(SUBSTITUTE(A$1:M$1,A4,"",1)+0,0))

 

[alansidman]

Here is an alternate solution using Power Query. Excel offers up multiple means to the end.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Column1.2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1.1"}, {{"Sum", each List.Sum([Column1.2]), type nullable text}})
in
    #"Grouped Rows"

 

[cyberdimitri]

Here is an alternate solution using Power Query. Excel offers up multiple means to the end.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Column1.2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1.1"}, {{"Sum", each List.Sum([Column1.2]), type nullable text}})
in
    #"Grouped Rows"

Wow, I got dizzy just looking at your solution, I wish I could be as fluent in excel as you guys are. Thanks for your answer, I didn't try it because Fluff's solution works perfectly and it was a one-liner, but I appreciate the effort!