Pivot Tables - Comparing Grand Totals to each other

[arveearv]

Hi:

Imagine a medical clinic where there are, say, 5 doctors who each have their own list of patients. Every once in a while, though, one doctor will pitch in and see the patient of a colleague.

Here is a sample set of data:

Here's a pivot table that summarizes the data:

So far, so good. But now I want to know, for each doctor, how much more (or less) they are contributing than receiving. Doctor2, for instance, has provided a Grand Total of 2 care units to patients that belong to someone else, but Doctor2's patients received 3 units of care from other doctors. So Doctor1's "Grand Total Difference" should be +1.

How can I calculate this, given that the Grand Totals columns will always be in different places, depending on how many doctors are providing care in a clinic?

Thanks!

 

[arveearv]

Hi:

I think I may have answered my own question. See if this makes sense. Alas, this is a time when the "Sumif" function trumps the Pivot Table!

Instead of using the Pivot Table data, I used the raw data (shown in the Top graphic, above). For each doctor, I created a new cell called "Balance". Here's the formula for "Doctor3":
=SUMIF(B:B,"Doctor3",D:D)-SUMIF(C:C,"Doctor3",D:D)

It works, but I think the Pivot Table met its match with this one.