I am trying to calculate costs for product we have to reship to patient,
taking into consideration whether or not we can receive credit from the
vendor. Here is the data.

Worksheet = jan
A B C D E
F
1 Ship Loc Ship Date Cost Order # Item
Return Reason
2 1 12/30/08 60.00 54585 Baseball bat
Damage
3 28 1/23/09 1.00 54858 Gatorade
Not needed
4 28 2/3/09 1000.00 58975 Team Uniforms Not
needed
5 1 12/28/09 50.00 68748 Glove
unknown
6 4 11/25/09 100.00 58789 Baseball cleats
wrong size
7 5 2/4/09 25.00 89758 Football
not needed

To get my actual cost I need to take the cost of the item add to it a $25
processing cost to ship pick/pack/ship order - the credit I will receive from
the vendor to return the product to them.

There are certain items that I will not get credit from the vendor no matter
what the return reason, so lets say "item", "gatorade", will not be credited
no matter what the reason is. Then there are specific return reasons that I
will not get any credit for, so lets say "return reason", "damage" i will not
get any credit. The items that I do get credit from the vendor for, I will
get a cost minus 5%. For an item that is eligible for credit that costs
$100.00 the credit i will received will be $95. So my actual total cost will
be 100.00 + 25.00 processing cost - $95.00 = $30.00. Not sure if a pivot
table would work, because I have over 40 return reasons, and over 1500
products, some of which i get credit for some of which i do not.

The data on costs is entered on worksheet named Summary

I am using Microsoft Excel 2007 (12.0.6331.5000) SP1 MSO (12.0.6213.1000)