Conditional Lookup Functions

R

Rlmccants

I am trying to return the Cell Address based on a lookup value in m
SalesID Range and a SalesPrice that is less than zero (I have numerou
product IDS and records associated with the same Sales ID).

Can anyone help
 
R

Rlmccants

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.
 
B

Bernie Deitrick

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:C10000))

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

Rlmccants,

Actually, on second thought, if you only have one negative number for each sales ticket, you could
either

1) use a pivot table, selecting MIN of sales
2) use a data filter, to show only negative numbers

Also, you should change the formula to

=SUMPRODUCT(($A$2:$A$10000=$D2)*($C$2:$C$10000<0)*($C$2:$C$10000))

so that if you enter it into cell E2, you could copy it down to match a list in column D. You could
create a list of unique values easily by using the advanced filter on A1:A10000 , clicking unique
records only and setting the copy to destination to D1.

HTH,
Bernie
MS Excel MVP


Bernie Deitrick said:
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:C10000))

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Top