Rlmccants,

With 178474 in cell D2, and with Sales Order ID in column A, Product ID in column B, and Sales Price
in column C, this formula will return -21550:

=SUMPRODUCT((A2:A10000=D2)*(C2:C10000<0)*(C2:C1000 0))

HTH,
Bernie
MS Excel MVP


"Rlmccants" wrote in message
...

Sales Order ID
178474
178474
178474
178474
178474
178474

Product ID
1730
1740
1800
1410
1000D
1605

Sales Price
$9,500.00
$4,950.00
$5,000.00
$0.00
($21,550.00)
$52,500.00

SalesOrderID, Product ID, and Sales Price represent 3 separate columns.
The negative sales amount ($21,550) is a discount that needs to be
prorated among the other products sold on this invoice. I've developed
a formula that performs the calculation, however it currently uses an
absolute cell reference to identify the Discount - which requires me to
manually update this cell reference for each change in Sales Order ID.
Since I have 10,000 records representing roughly 2,000 sales, I need to
find a scaleable solution.

I've been trying to use the Address Function along with Index and
Match, but I haven't had any luck yet.

Any help you can provide would be great.


--
Rlmccants
------------------------------------------------------------------------
Rlmccants's Profile: http://www.excelforum.com/member.php...o&userid=26097
View this thread: http://www.excelforum.com/showthread...hreadid=395007