Calculation with multiple numbers in single cell

[TL30N]

Morning everyone,

I have a test for you all.

I have a schedule of activities that people take part in every day.

It is broken up into half an hour slots.

e.g. 08:00 - 0830 people do 1,2,3,4

1,2,3 & 4 being 4 different activities, which equates to 7.5 minutes per activity.

I'm looking for a way to calculate for the whole week how many minutes each activity has spent on it.

It's difficult as the half an hour slots don't all have the same number of activities, but more problematic is that I can't find a way to calculate calculate a cell that has 1,2,3,4,5 in it as 6 minutes of "1", 6 minutes of "2" etc. Please see below




[TABLE="width: 835"]
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]TIME[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]6.00-6.30[/TD]
[TD]60[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6.30-7.00[/TD]
[TD]60[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7.00-7.30[/TD]
[TD]60[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7.30-8.00[/TD]
[TD]60[/TD]
[TD]1,2,3,4,5,6,18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]56,57,4,5,6[/TD]
[/TR]
[TR]
[TD]8.00 - 8.30[/TD]
[TD]60[/TD]
[TD]1,2,3,4,5,6,18,21,56[/TD]
[TD]1, 2, 3, 4, 5, 6, 18, 21[/TD]
[TD]1,2,3,4,5,6,18,21[/TD]
[TD]1,2,3,4,5,6,18,21[/TD]
[/TR]
[TR]
[TD]8.30 - 9.00[/TD]
[TD]60[/TD]
[TD]18, 56,57[/TD]
[TD]1, 2, 3, 4, 5, 6, 18[/TD]
[TD]56,57,44[/TD]
[TD]9,21,56,57[/TD]
[/TR]
[TR]
[TD]9.00 - 9.30[/TD]
[TD]60[/TD]
[TD]55[/TD]
[TD]55, 56, 57, 47, 15[/TD]
[TD]44,47[/TD]
[TD]5,6,16,56[/TD]
[/TR]
[TR]
[TD]9.30 - 10.00[/TD]
[TD]60[/TD]
[TD]55[/TD]
[TD]5, 9,15, 57, 55[/TD]
[TD]47,28,27[/TD]
[TD]5,6,16[/TD]
[/TR]
[TR]
[TD]10.00 - 10.30[/TD]
[TD]60[/TD]
[TD]47,28,56,57[/TD]
[TD]5,6,16[/TD]
[TD]47,63[/TD]
[TD]5,6,16[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nishant94]

With data like this:

Book1
	A	B	C	D	E	F
1	TIME	Monday	Tuesday	Wednesday	Thursday	Friday
2	6.00-6.30	60
3	6.30-7.00	60
4	7.00-7.30	60
5	7.30-8.00	60	1,2,3,4,5,6,18			56,57,4,5,6
6	8.00 - 8.30	60	1,2,3,4,5,6,18,21,56	1, 2, 3, 4, 5, 6, 18, 21	1,2,3,4,5,6,18,21	1,2,3,4,5,6,18,21
7	8.30 - 9.00	60	18, 56,57	1, 2, 3, 4, 5, 6, 18	56,57,44	9,21,56,57
8	9.00 - 9.30	60	55	55, 56, 57, 47, 15	44,47	5,6,16,56
9	9.30 - 10.00	60	55	5, 9,15, 57, 55	47,28,27	5,6,16
10	10.00 - 10.30	60	47,28,56,57	5,6,16	47,63	5,6,16
Sheet2

Answer:

Book1
	A	B
12	Activities	Time(In Minutes)
13	1	23.15
14	2	23.15
15	3	23.15
16	4	29.15
17	5	72.65
18	6	66.65
19	7	0.00
20	8	0.00
21	9	13.50
22	10	0.00
23	11	0.00
24	12	0.00
25	60	270.00
Sheet2

Cell Formulas
Range	Formula
B13	{=SUM(IFERROR(30/IF(ISNUMBER(SEARCH(","&A13&",",","&SUBSTITUTE($B$2:$F$10," ","")&",")),LEN(","&$B$2:$F$10&",")-LEN(SUBSTITUTE($B$2:$F$10,",",""))-1),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[TL30N]

You are brilliant.

Thank you