Multiple criteria and lists for one result

**gyj** · 06-14-2012, 03:45 PM

HELP! I can't seem to get this to work.

I need to be able to determine how many points each Sales rep earn for selling eligible products. Of course it's not just that simple. The requirements and rules are as follows: sell any SKU form list A, AND sell any SKU from list B, with the same PO#, receive points.

1. A PO must contain at least 2 eligible SKUs: 1 from eligible SKUs list "A" AND 1 from eligilble SKUs list B (different SKUs in each list)
2. BOTH of the eligible SKUs from the lists must have the same PO# (i.e. they must be made in the same sale)
3. If criteria 1 and 2 are met, sales rep receive points.
4. Points can be tricky also, please see example lists attached.

Please see the 3 different types of lists I have to work with to accomplish this.Attachment 162061

Columns A,B,C = Sales Data
Columns E, F = Eligible SKUs
Columns H, I, J = Points

**rylo** · 06-14-2012, 08:20 PM

Hi

About the only way I could get this to function is with a UDF.

Create a module in the example workbook, and add the code

Please Login or Register  to view this content.

Then go to the spreadsheet and in D2 enter:
=IF(COUNTIF($C$2:C2,C2)=1,myfunc(C2,$C$2:$C$26,$E$2:$E$5,$F$2:$F$8),"")

I've copied this down to D26 as I've put in some extra items to try to test the SKU D / SKU L combinations.

See how it goes.

rylo