Summing numbers in cells with alphanumeric content

[Tpid]

I am doing a paired comparison analysis and have an Excel table where each cell contains one letter followed by one number. I want to sum the numbers for each unique letter. E.g. If there are 7 cells that begin with 'C', I want to sum the numbers that follow the 'C'. If there are 8 cells with the letter 'U', I want to sum the numbers in those three cells. I want the results of the sum for each unique letter, to be shown in a separate cell distinct to that letter (the yellow shaded boxes in my image below). If the data in my full table is contained in cells B5 to K14, I want the total of the 'C's to be in placed in cell B4. I want the total of the cells with 'U' to be placed in cell C5. I want the total of the next letter to be placed in cell D6, etc. (along the diagonal). I suspect I'll need to enter a separate formula into each of my target, diagonal cells ... but I am not clear on what that formula should be. I've read a little about SUMIF, but have not been able to master it so clearly am missing something. I'd be very grateful for any big brain help. Thankyou.

 

[Dave Patton]

Adjust the ranges and references as required and try

T202203.xlsm
	A	B	C
1		Confidence	Understanding
2		12	6
3		C2	C2
4		D3	D3
5		C3	C3
6		C7	C7
7		U2	U2
8		U4	U4
9
2a

Cell Formulas
Range	Formula
B2:C2	B2	=SUMPRODUCT(--(LEFT(B3:B8)=LEFT(B1,1)),--(RIGHT(B3:B8,1)))

 

[Tpid]

Adjust the ranges and references as required and try

T202203.xlsm
	A	B	C
1			Confidence
2			12
3		C2
4		D3
5		C3
6		C7
2a

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(LEFT(B3:B6)=LEFT(C1,1)),--(RIGHT(B3:B6,1)))

N.B. You can post an extract of your sheet with the forum's tool called XL2BB.

Dave, thank you for the super speedy reply. Forgive my inexperience ... in the sample formula you shared, what do the various parameters mean? SUMPRODUCT I get ... but what do the LEFT ... LEFT ... RIGHT ... parameters mean?

 

[Dave Patton]

N.B. You can paste my post into a clean sheet.
Click on the icon below the F(x) in the heading of the example and then move to your sheet and paste into cell A1

Left and Right are Excel functions see Excel's help for complete information.
LEFT(B1,1) finds the first letter in the word located in cell B1

Try using Excel's Formula Evaluate to review the details of the calculation.

The double negative -- coerces the results to numeric.

 

[jtakw]

Hi,

Please consider providing sample using XL2BB (see my signature), or at least in Table format, so helpers don't have to manually type your data for testing.

C5 formula can be copied pasted up to K13.

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
3		Co	Un	Ac	Va	St	Ex	Le	Ge	Bu	Fi	Re
4	Co	18
5	Un	U3	20
6	Ac	C2	A1	21
7	Va	C3	U3	A3	1
8	St	C2	U2	A2	S3	5
9	Ex	C3	U3	A3	E2	0	3
10	Le	0	U1	A2	L2	0	L3	8
11	Ge	G1	0	A2	G3	G2	G3	G2	19
12	Bu	C3	U3	A3	V1	S1	0	L1	G3	1
13	Fi	C3	U3	A3	F1	S1	E1	L2	G3	B1	1
14	Re	C2	U2	A2	R2	R2	R2	R2	G2	R1	R1	10
Sheet1032

Cell Formulas
Range	Formula
B4	B4	=SUMPRODUCT((LEFT(B5:B$14)=LEFT($A4))*RIGHT(B5:B$14))
C5,K13,J12,I11,H10,G9,F8,E7,D6	C5	=SUMPRODUCT((LEFT(C6:C$14)=LEFT($A5))*RIGHT(C6:C$14))+SUMPRODUCT((LEFT($B5:B5)=LEFT($A5))*RIGHT($B5:B5))
L14	L14	=SUMPRODUCT((LEFT($B14:K14)=LEFT($A14))*RIGHT($B14:K14))

 

[Tpid]

N.B. You can paste my post into a clean sheet.
Click on the icon below the F(x) in the heading of the example and then move to your sheet and paste into cell A1

Left and Right are Excel functions see Excel's help for complete information.
LEFT(B1,1) finds the first letter in the word located in cell B1

Try using Excel's Formula Evaluate to review the details of the calculation.

The double negative -- coerces the results to numeric.

Thank you Dave. I appreciate the great information!

 

[Tpid]

Hi,

Please consider providing sample using XL2BB (see my signature), or at least in Table format, so helpers don't have to manually type your data for testing.

C5 formula can be copied pasted up to K13.

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
3		Co	Un	Ac	Va	St	Ex	Le	Ge	Bu	Fi	Re
4	Co	18
5	Un	U3	20
6	Ac	C2	A1	21
7	Va	C3	U3	A3	1
8	St	C2	U2	A2	S3	5
9	Ex	C3	U3	A3	E2	0	3
10	Le	0	U1	A2	L2	0	L3	8
11	Ge	G1	0	A2	G3	G2	G3	G2	19
12	Bu	C3	U3	A3	V1	S1	0	L1	G3	1
13	Fi	C3	U3	A3	F1	S1	E1	L2	G3	B1	1
14	Re	C2	U2	A2	R2	R2	R2	R2	G2	R1	R1	10
Sheet1032

Cell Formulas
Range	Formula
B4	B4	=SUMPRODUCT((LEFT(B5:B$14)=LEFT($A4))*RIGHT(B5:B$14))
C5,K13,J12,I11,H10,G9,F8,E7,D6	C5	=SUMPRODUCT((LEFT(C6:C$14)=LEFT($A5))*RIGHT(C6:C$14))+SUMPRODUCT((LEFT($B5:B5)=LEFT($A5))*RIGHT($B5:B5))
L14	L14	=SUMPRODUCT((LEFT($B14:K14)=LEFT($A14))*RIGHT($B14:K14))

Thank you so very much! This was my first time posting a question, and I now understand why just including an 'image' of my table, rather than attaching a mini-sheet was not a smart thing to do. I sincerely appreciate your help.

 

[jtakw]

You're welcome, thanks for the feedback.