Unique/Distinct Value Help

[thp510]

Hello. I have multiple rows of sales data where the amount, commitment date, and the company is the same but the previous commitment dates varies. I'm trying to see how much amount move from one quarter (Q4) to another quarter (Q1). See the attachment pic for a sample data set.

I'm want to get just the distinct amount by each company but when I try to pivot it, I get the total. So for example, in Company X I want to see $30,000 as the amount that got pushed from Q3 to Q4 and not have it sum up $30,000 x 4 times for a wrong total of $120,000.

Any idea how I can do this easily in Excel?

 

[etaf]

if they are always the same amount for each company
in a pivot table
Rather than using SUM in field settings
use average or max or min