Calculating total hours against a target

[willanss]

Hi everyone. I thought I had a simple problem but after much cursing I'm giving in and asking for help. I need to count the total amount of hours training agents have received and show how much is still required to meet their target of 15 hours.

The raw data looks like this

Agent 1	2:30
Agent 1	1:00
Agent 2	4:00
Agent 2	3:20
Agent 2	1:00

I've set up a pivot that shows the total hours each agent has done, but I can't get a value for how many hours are needed to bring them up to 15. I did get this to work by having an external calculation that references the pivot, but this isn't really the nicest way of doing it. I've tried adding a calculated field, but this isn't working correctly either.

Does anyone have any ideas? I'm stumped.

 

[etaf]

Not sure what version of Excel you are using , if latest version, then you can use UNIQUE()

Book7
	A	B	C	D	E	F	G
1	Agent	Hours			Agent	Time	Hours left
2	Agent 1	2:30			Agent 1	3:30	11:30
3	Agent 1	1:00			Agent 2	8:20	6:40
4	Agent 2	4:00
5	Agent 2	3:20
6	Agent 2	1:00
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=UNIQUE(A2:A6,FALSE,FALSE)
F2:F3	F2	=SUMIF($A$2:$A$6,E2,$B$2:$B$6)
G2:G3	G2	=TIMEVALUE("15:00")-F2
Dynamic array formulas.