Sum a cell that contains multiple numbers that represent other values

[NateTiberius]

Hey.

I have hundreds of cells that each contain different number codes that represent another value. For instance, a cell might contain, "4, 16, 32". Each of those distinct numbers correlates to other values. For instance, "4" equals 3.58, 16 equals 2.67, and 32 equals 2.75, etc. What I need is a formula that will read those number codes in the cell and then sum the numbers they represent. So, in the case of the "4, 16, 32" cell, it would sum to 9.00.

So, using the fourth formula listed here – How to use Excel formula: "If cell contains" | SoftwareKeep – I was able to ALMOST make this happen. Below is an example formula:

=IF(OR(ISNUMBER(SEARCH("4",H2))),F5)+IF(OR(ISNUMBER(SEARCH("16",H2))),F17)+IF(OR(ISNUMBER(SEARCH("32",H2))),F33)

Using that formula gets me my number I want for the above example, but there's a problem. There are codes that range from 1 through 38. So one cell might only have a "1", another only a "5", and another a "15". However, using that formula, when it reads a "15", it sees it as not only a "15", but as a "1", a "5", AND a "15". So it adds three numbers together instead of only the 15.

I thought I could "outsmart" that by instead having the formula search for "1," (with a comma). But that does not work because certain cells END in single digit number (or, as mentioned above, ONLY contain a single number): for instance, "15, 6, 1". So using the "1," method, causes the formula not to find that cell-ending "1". Below is the full formula I was using to add all the codes from 1 through 38 (I purposefully left out a couple numbers).

=IF(OR(ISNUMBER(SEARCH("1,",'33122 Raw'!C2))),Key!F2)+IF(OR(ISNUMBER(SEARCH("2,",'33122 Raw'!C2))),Key!F3)+IF(OR(ISNUMBER(SEARCH("3,",'33122 Raw'!C2))),Key!F4)+IF(OR(ISNUMBER(SEARCH("4,",'33122 Raw'!C2))),Key!F5)+IF(OR(ISNUMBER(SEARCH("5,",'33122 Raw'!C2))),Key!F6)+IF(OR(ISNUMBER(SEARCH("6,",'33122 Raw'!C2))),Key!F7)+IF(OR(ISNUMBER(SEARCH("8,",'33122 Raw'!C2))),Key!F9)+IF(OR(ISNUMBER(SEARCH("9,",'33122 Raw'!C2))),Key!F10)+IF(OR(ISNUMBER(SEARCH("10",'33122 Raw'!C2))),Key!F11)+IF(OR(ISNUMBER(SEARCH("11",'33122 Raw'!C2))),Key!F12)+IF(OR(ISNUMBER(SEARCH("12",'33122 Raw'!C2))),Key!F13)+IF(OR(ISNUMBER(SEARCH("13",'33122 Raw'!C2))),Key!F14)+IF(OR(ISNUMBER(SEARCH("14",'33122 Raw'!C2))),Key!F15)+IF(OR(ISNUMBER(SEARCH("15",'33122 Raw'!C2))),Key!F16)+IF(OR(ISNUMBER(SEARCH("16",'33122 Raw'!C2))),Key!F17)+IF(OR(ISNUMBER(SEARCH("17",'33122 Raw'!C2))),Key!F18)+IF(OR(ISNUMBER(SEARCH("18",'33122 Raw'!C2))),Key!F19)+IF(OR(ISNUMBER(SEARCH("19",'33122 Raw'!C2))),Key!F20)+IF(OR(ISNUMBER(SEARCH("20",'33122 Raw'!C2))),Key!F21)+IF(OR(ISNUMBER(SEARCH("21",'33122 Raw'!C2))),Key!F22)+IF(OR(ISNUMBER(SEARCH("22",'33122 Raw'!C2))),Key!F23)+IF(OR(ISNUMBER(SEARCH("23",'33122 Raw'!C2))),Key!F24)+IF(OR(ISNUMBER(SEARCH("27",'33122 Raw'!C2))),Key!F28)+IF(OR(ISNUMBER(SEARCH("28",'33122 Raw'!C2))),Key!F29)+IF(OR(ISNUMBER(SEARCH("30",'33122 Raw'!C2))),Key!F31)+IF(OR(ISNUMBER(SEARCH("30",'33122 Raw'!C2))),Key!F31)+IF(OR(ISNUMBER(SEARCH("31",'33122 Raw'!C2))),Key!F32)+IF(OR(ISNUMBER(SEARCH("32",'33122 Raw'!C2))),Key!F33)+IF(OR(ISNUMBER(SEARCH("33",'33122 Raw'!C2))),Key!F34)+IF(OR(ISNUMBER(SEARCH("34",'33122 Raw'!C2))),Key!F35)+IF(OR(ISNUMBER(SEARCH("35",'33122 Raw'!C2))),Key!F36)+IF(OR(ISNUMBER(SEARCH("36",'33122 Raw'!C2))),Key!F37)+IF(OR(ISNUMBER(SEARCH("38",'33122 Raw'!C2))),Key!F39)

Long story short, does anyone have a formula to accomplish this goal?

 

[mumps]

I would hate to work with such a long formula. Have you considered using a macro? I can't promise a solution, but if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet, I could have a look at it. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Fluff]

Can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also do you have the TEXTSPLIT function yet?

 

[Snakehips]

@NateTiberius Might something like below help?

Requires a reference table to define relate the text and sum values.

Book1
	A	B	C	D	E
1	Text #	Value		Text	Sum
2	1	3.2		4,16,32	9.00
3	2	3.2		2,6,34	9.60
4	3	3.2		10	3.20
5	4	3.58		1,10	6.40
6	5	3.2		10,1	6.40
7	6	3.2		10,1,4	9.98
8	7	3.2		7,3,2,8	12.80
9	8	3.2		32	2.75
10	9	3.2			0.00
11	10	3.2
12	11	3.2
13	12	3.2
14	13	3.2
15	14	3.2
16	15	3.2
17	16	2.67
18	17	3.2
19	18	3.2
20	19	3.2
21	20	3.2
22	21	3.2
23	22	3.2
24	23	3.2
25	24	3.2
26	25	3.2
27	26	3.2
28	27	3.2
29	28	3.2
30	29	3.2
31	30	3.2
32	31	3.2
33	32	2.75
34	33	3.2
35	34	3.2
36	35	3.2
37	36	3.2
38	37	3.2
Sheet2

Cell Formulas
Range	Formula
E2:E10	E2	=SUM(SUMIF($A$2:$A$39,TEXTSPLIT(D2,","),$B$2:$B$39))

 

[NateTiberius]

Can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also do you have the TEXTSPLIT function yet?

I do not have the TEXTSPLIT function yet. Also, below is a minisheet using the XL2BB add-in.

03457816.XLSX
	A	B	C	D	E	F	G	H
1	KEY CODES	Total Hours		Employee	1-Jan	2-Jan	3-Jan	4-Jan
2	1	3.25		A	20	20
3	2	2.75		B	12, 13, 16	12, 13	12, 16, 13	12, 3, 13
4	3	0.50		C	36, 37, 38	36, 37, 38	36, 37, 38	36, 37, 38
5	4	3.58		D		4	8, 9, 4	15, 17
6	5	2.00		E	30, 32	30, 32, 16, 17	30, 32	Vacation
7	6	2.00		F	23,24,25,26,28,29	23,24,25,26,28,29	23,24,25,26,28,29	23,24,25,26,28,29
8	7	1.25		G	5, 22	5, 6, 19	21, 22, 19	21, 22, 19
9	8	1.75		H
10	9	1.25		I	18		20
11	10	1.75		J	19, 10	10	2, 10	2, 33
12	11			K	31, 33	31, 33	31, 33	31
13	12	2.50		L
14	13	2.50		M		18		18, 20
15	14	1.25		N	21	21, 22
16	15	3.08		O	15, 3, 6	15, 3	15, 3	8, 9
17	16	2.67		P	8, 9, 17	8, 9, 20	18, 17	30, 32
18	17	2.83		Q	11, 11, 35	11, 11, 35	11, 11, 35	11, 11, 35, 6
19	18	3.50		R	2, 4	2	5, 6	5, 4
20	19	2.50
21	20	2.00		Employee	1-Jan	2-Jan	3-Jan	4-Jan
22	21	2.50		A	2.00	2.00
23	22	2.50		B	7.67	5.00	7.67	5.50
24	23	5.50		C	9.83	9.83	9.83	9.83
25	24
26	25			Above is an example of who they SHOULD sum to (I manually did these)
27	26
28	27	1.25
29	28	2.50
30	29
31	30	2.25
32	31	3.25
33	32	2.75
34	33	3.58
35	34	3.00
36	35	5.00
37	36	6.08
38	37
39	38	3.75
Key

Cell Formulas
Range	Formula
E22	E22	=B21
F22	F22	=B21
E23	E23	=B13+B14+B17
F23	F23	=B13+B14
G23	G23	=B13+B17+B14
H23	H23	=B13+B4+B14
E24:H24	E24	=$B$37+$B$38+$B$39

 

[NateTiberius]

@NateTiberius Might something like below help?

Requires a reference table to define relate the text and sum values.

Book1
	A	B	C	D	E
1	Text #	Value		Text	Sum
2	1	3.2		4,16,32	9.00
3	2	3.2		2,6,34	9.60
4	3	3.2		10	3.20
5	4	3.58		1,10	6.40
6	5	3.2		10,1	6.40
7	6	3.2		10,1,4	9.98
8	7	3.2		7,3,2,8	12.80
9	8	3.2		32	2.75
10	9	3.2			0.00
11	10	3.2
12	11	3.2
13	12	3.2
14	13	3.2
15	14	3.2
16	15	3.2
17	16	2.67
18	17	3.2
19	18	3.2
20	19	3.2
21	20	3.2
22	21	3.2
23	22	3.2
24	23	3.2
25	24	3.2
26	25	3.2
27	26	3.2
28	27	3.2
29	28	3.2
30	29	3.2
31	30	3.2
32	31	3.2
33	32	2.75
34	33	3.2
35	34	3.2
36	35	3.2
37	36	3.2
38	37	3.2
Sheet2

Cell Formulas
Range	Formula
E2:E10	E2	=SUM(SUMIF($A$2:$A$39,TEXTSPLIT(D2,","),$B$2:$B$39))

I posted a minisheet in my reply above if you'd be kind enough to take a look. Your sheet would work if there weren't multiple values that needed summing in each cell.

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	KEY CODES	Total Hours		Employee	32874	32875	32876	32877
2	1	3.25		A	20	20
3	2	2.75		B	12, 13, 16	12, 13	12, 16, 13	12, 3, 13
4	3	0.50		C	36, 37, 38	36, 37, 38	36, 37, 38	36, 37, 38
5	4	3.58		D		4	8, 9, 4	15, 17
6	5	2.00		E	30, 32	30, 32, 16, 17	30, 32	Vacation
7	6	2.00		F	23,24,25,26,28,29	23,24,25,26,28,29	23,24,25,26,28,29	23,24,25,26,28,29
8	7	1.25		G	5, 22	5, 6, 19	21, 22, 19	21, 22, 19
9	8	1.75		H
10	9	1.25		I	18		20
11	10	1.75		J	19, 10	10	2, 10	2, 33
12	11			K	31, 33	31, 33	31, 33	31
13	12	2.50		L
14	13	2.50		M		18		18, 20
15	14	1.25		N	21	21, 22
16	15	3.08		O	15, 3, 6	15, 3	15, 3	8, 9
17	16	2.67		P	8, 9, 17	8, 9, 20	18, 17	30, 32
18	17	2.83		Q	11, 11, 35	11, 11, 35	11, 11, 35	11, 11, 35, 6
19	18	3.50		R	2, 4	2	5, 6	5, 4
20	19	2.50
21	20	2.00		Employee	32874	32875	32876	32877
22	21	2.50		A	2.00	2.00	0.00	0.00
23	22	2.50		B	7.67	5.00	7.67	5.50
24	23	5.50		C	9.83	9.83	9.83	9.83
25	24			D	0.00	3.58	6.58	5.92
26	25			E	5.00	10.50	5.00	0.00
27	26			F	8.00	8.00	8.00	8.00
28	27	1.25		G	4.50	6.50	7.50	7.50
29	28	2.50		H	0.00	0.00	0.00	0.00
30	29			I	3.50	0.00	2.00	0.00
31	30	2.25		J	4.25	1.75	4.50	6.33
32	31	3.25		K	6.83	6.83	6.83	3.25
33	32	2.75		L	0.00	0.00	0.00	0.00
34	33	3.58		M	0.00	3.50	0.00	5.50
35	34	3.00		N	2.50	5.00	0.00	0.00
36	35	5.00		O	5.58	3.58	3.58	3.00
37	36	6.08		P	5.83	5.00	6.33	5.00
38	37			Q	5.00	5.00	5.00	7.00
39	38	3.75		R	6.33	2.75	4.00	5.58
Data

Cell Formulas
Range	Formula
E22:H39	E22	=SUM(SUMIFS($B:$B,$A:$A,FILTERXML("<k><m>"&SUBSTITUTE(E2,",","</m><m>")&"</m></k>","//m")))

 

[NateTiberius]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	KEY CODES	Total Hours		Employee	32874	32875	32876	32877
2	1	3.25		A	20	20
3	2	2.75		B	12, 13, 16	12, 13	12, 16, 13	12, 3, 13
4	3	0.50		C	36, 37, 38	36, 37, 38	36, 37, 38	36, 37, 38
5	4	3.58		D		4	8, 9, 4	15, 17
6	5	2.00		E	30, 32	30, 32, 16, 17	30, 32	Vacation
7	6	2.00		F	23,24,25,26,28,29	23,24,25,26,28,29	23,24,25,26,28,29	23,24,25,26,28,29
8	7	1.25		G	5, 22	5, 6, 19	21, 22, 19	21, 22, 19
9	8	1.75		H
10	9	1.25		I	18		20
11	10	1.75		J	19, 10	10	2, 10	2, 33
12	11			K	31, 33	31, 33	31, 33	31
13	12	2.50		L
14	13	2.50		M		18		18, 20
15	14	1.25		N	21	21, 22
16	15	3.08		O	15, 3, 6	15, 3	15, 3	8, 9
17	16	2.67		P	8, 9, 17	8, 9, 20	18, 17	30, 32
18	17	2.83		Q	11, 11, 35	11, 11, 35	11, 11, 35	11, 11, 35, 6
19	18	3.50		R	2, 4	2	5, 6	5, 4
20	19	2.50
21	20	2.00		Employee	32874	32875	32876	32877
22	21	2.50		A	2.00	2.00	0.00	0.00
23	22	2.50		B	7.67	5.00	7.67	5.50
24	23	5.50		C	9.83	9.83	9.83	9.83
25	24			D	0.00	3.58	6.58	5.92
26	25			E	5.00	10.50	5.00	0.00
27	26			F	8.00	8.00	8.00	8.00
28	27	1.25		G	4.50	6.50	7.50	7.50
29	28	2.50		H	0.00	0.00	0.00	0.00
30	29			I	3.50	0.00	2.00	0.00
31	30	2.25		J	4.25	1.75	4.50	6.33
32	31	3.25		K	6.83	6.83	6.83	3.25
33	32	2.75		L	0.00	0.00	0.00	0.00
34	33	3.58		M	0.00	3.50	0.00	5.50
35	34	3.00		N	2.50	5.00	0.00	0.00
36	35	5.00		O	5.58	3.58	3.58	3.00
37	36	6.08		P	5.83	5.00	6.33	5.00
38	37			Q	5.00	5.00	5.00	7.00
39	38	3.75		R	6.33	2.75	4.00	5.58
Data

Cell Formulas
Range	Formula
E22:H39	E22	=SUM(SUMIFS($B:$B,$A:$A,FILTERXML("<k><m>"&SUBSTITUTE(E2,",","</m><m>")&"</m></k>","//m")))

That is beautiful. It works for what I need. Thank you so much! One issue though, when I copied that formula over, it keeps wanting me to manually put each cell as an array. So I'm having to go through each cell and click ctrl-shift-enter. Any idea why it's doing that? Thanks!

 

[Fluff]

it keeps wanting me to manually put each cell as an array

What makes you say that?

 

[NateTiberius]

What makes you say that?

Because the formula isn't copying to each cell properly. When I go to drag E22, for instance, to the cells below, all it's doing is dragging the "2.0" down (I am making sure the cell it's referencing is appropriate too). Then, when I hit ctrl-shift-enter, it puts it into an array and calculates it properly. But as soon as the array is gone, it goes back to rendering an inaccurate calculation.