VLOOKUP Help - Combining several cells (example included)

[wesleyterrill]

Hey guys! I'm doing some time analysis and I was hoping someone could offer some help. Basically, I've broken down my days into several different categories and I'd like to summarize the total of each category for each day. Kind of hard to explain, see my example for clarification Thank you for any help!


This is a general example of what my time tracking looks like. For Day 1, I'd like to combine all categories into one total for each day... so Day 1 will have 4 hours of Admin, 1 hour of Meeting, and 2 hours of Production. I know one formula for counting hours would look like: =TEXT(B4-B3, "h:mm") *[h is for hours, mm is for minutes]. I'm assuming I will have to combine that formula with a VLOOKUP to get the results I want. I'm sure there are other ways to go about it, any suggestions would be greatly appreciated!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Category[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Admin
[/TD]
[TD]8:00 AM[/TD]
[TD]10:00 AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Meeting[/TD]
[TD]10:00 AM[/TD]
[TD]11:00 AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Admin[/TD]
[TD]11:00 AM[/TD]
[TD]12:30 PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Admin[/TD]
[TD]1:30 PM[/TD]
[TD]2:00 PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Production[/TD]
[TD]2:00 PM[/TD]
[TD]4:00 PM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Meeting[/TD]
[TD]8:00 AM[/TD]
[TD]9:00 AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Training[/TD]
[TD]9:00 AM[/TD]
[TD]12:00 PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Admin[/TD]
[TD]1:00 PM[/TD]
[TD]2:00 PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Production[/TD]
[TD]2:00 PM[/TD]
[TD]3:30 PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Admin[/TD]
[TD]3:30 PM[/TD]
[TD]4:00 PM[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Production[/TD]
[TD]8:00 AM[/TD]
[TD]11:30 AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Admin[/TD]
[TD]11:30 AM[/TD]
[TD]12:00 PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Production[/TD]
[TD]1:00 PM[/TD]
[TD]4:00 PM[/TD]
[/TR]
</tbody>[/TABLE]

To reiterate, I'd like to consolidate each category into a daily total (of time in hours). This is what my results should look like:

[TABLE="width: 600"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Admin[/TD]
[TD]Meeting[/TD]
[TD]Production[/TD]
[TD]Training[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.5 (or 1:30, both work)[/TD]
[TD]1[/TD]
[TD]1.5 (or 1:30, both work)[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD].5 (or 0:30, both work)[/TD]
[TD]0[/TD]
[TD]6.5 (or 6:30, both work)[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the consideration, everyone. Any help would be greatly appreciated! Let me know if you need any clarification

Wes

 

[63falcondude]

Try something like this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Day	Category	Start Time	End Time		Day	Admin	Meeting	Production	Training
2	1	Admin	8:00 AM	10:00 AM		1	4.0	1.0	2.0	0.0
3	1	Meeting	10:00 AM	11:00 AM		2	1.5	1.0	1.5	3.0
4	1	Admin	11:00 AM	12:30 PM		3	0.5	0.0	6.5	0.0
5	1	Admin	1:30 PM	2:00 PM
6	1	Production	2:00 PM	4:00 PM
7	2	Meeting	8:00 AM	9:00 AM
8	2	Training	9:00 AM	12:00 PM
9	2	Admin	1:00 PM	2:00 PM
10	2	Production	2:00 PM	3:30 PM
11	2	Admin	3:30 PM	4:00 PM
12	3	Production	8:00 AM	11:30 AM
13	3	Admin	11:30 AM	12:00 PM
14	3	Production	1:00 PM	4:00 PM
Sheet1

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(($A$2:$A$14=$F2)*($B$2:$B$14=G$1)*($D$2:$D$14-$C$2:$C$14))*24

 

[63falcondude]

There are a few simple ways to fill in column A.

One is to enter the formula =A1 in A2 and then copy A2, select all of column A, Home: Find & Select > Go To Special > Blanks > OK, then paste.

Now copy column A and paste values to get rid of the formulas.

 

[SpillerBD]

It's a Pivot Table solution.
You can add a duration calculation to your source table or do a calculated field for duration.
If you want H:MM duration use the format "[H]:MM:SS"