Which functions will work in this case?

[ajrilo]

I have 4 teams that committed to doing volunteer work throughout the year. I set up a calendar and want to track teams and hours buy placing a 4-2, 3-1, 2-4, on the calendar, the first number representing the team and the second number represents the hours. I formatted the cells in the calendar as text. what function do I use now so that excel can distinguish between all four teams ( the first number) and add the correct number of hours to the team totals at the bottom. I added the numbers manually and placed by the teams to demonstrate what I would like excel to do for me.

 

[Eric W]

Welcome to the MrExcel forum!

Perhaps:

Book1
	A	B	C
1	Teams
2	4-2
3	3-1
4	2-4
5	4-11
6	1-1
7
8
9		Team	Hours
10		1	1
11		2	4
12		3	1
13		4	13
Sheet3

Cell Formulas
Range	Formula
C10:C13	C10	=SUMPRODUCT(--(LEFT("0"&A$2:A$20,2)+0=B10),(MID(A$2:A$20,3,9)&".0")+0)

 

[ajrilo]

Welcome to the MrExcel forum!

Perhaps:

Book1
	A	B	C
1	Teams
2	4-2
3	3-1
4	2-4
5	4-11
6	1-1
7
8
9		Team	Hours
10		1	1
11		2	4
12		3	1
13		4	13
Sheet3

Cell Formulas
Range	Formula
C10:C13	C10	=SUMPRODUCT(--(LEFT("0"&A$2:A$20,2)+0=B10),(MID(A$2:A$20,3,9)&".0")+0)

Eric,

Thank you so much! what if a team only does a half hour, I entered 4-1.5 and got an error, what must I modify to not get an error?

Tony

 

[Eric W]

I had to get a little tricky to accommodate empty cells in your list, otherwise the formula would be a bit simpler. But I think this version should handle decimal places:

Book1
	A	B	C
1	Teams
2	4-2.5
3	3-1
4	2-4
5	4-11
6	1-1
7
8
9		Team	Hours
10		1	1
11		2	4
12		3	1
13		4	13.5
Sheet3

Cell Formulas
Range	Formula
C10:C13	C10	=SUMPRODUCT(--(LEFT("0"&A$2:A$20,2)+0=B10),("0"&MID(A$2:A$20,3,9))+0)

 

[ajrilo]

I had to get a little tricky to accommodate empty cells in your list, otherwise the formula would be a bit simpler. But I think this version should handle decimal places:

Book1
	A	B	C
1	Teams
2	4-2.5
3	3-1
4	2-4
5	4-11
6	1-1
7
8
9		Team	Hours
10		1	1
11		2	4
12		3	1
13		4	13.5
Sheet3

Cell Formulas
Range	Formula
C10:C13	C10	=SUMPRODUCT(--(LEFT("0"&A$2:A$20,2)+0=B10),("0"&MID(A$2:A$20,3,9))+0)

Eric,

Again thank you, works like a dream!

Tony