Hi there folks,

I have another conundrum. I'm sure this one is also easy - but it must be because it's Friday afternoon I can't figure it out.

I have a rota in Excel where my rows are days. One of my agents performs on-call each day and "gains" time. So this is a two column table, with one column the agent initials, and the next column the accumulated time.

Next to this I have another two columns for the loss of accumulated time. In theory, multiple agents could "use" time they have accumulated on a single day, but to keep it simple lets assume that just one agent can.

So now I have four columns:

Agent | Time gained this day | Agent | Time used this day
AB | 2.5 | CD | 1
AB | 2.5 | |
AB | 2.5 | |
AB | 1.5 | EF | 2.5
CD | 2.5 | AB | 1.5
etc.

What I'm trying to do is basically provide a summary of what each's agents accumulated time is. Total time accumulated is a simple pivot table, as is total time gained. But what I can't seem to do is combine the agent columns into a single column if you like so that the pivot table basically sums one column and subtracts the equivalent used time from the other.

So what I would hope as an output would be:

Agent | Total Time accumulated over period | Total time used over period | Currently available time
AB | 9 | 1.5 | 7.5
CD | 2.5 | 1 | 1.5
EF | 0 | 2.5 | -2.5


Any ideas anyone?

Thanks!

Clogs.