Determining Values Based on Multiple Criteria

[chicagolarsons]

Hi, I need help figuring out a formula. Report is tracking spend if a supplier is based in the state of Illinois and/or if the supplier is certified as "Diverse."

I have a lookup table with the suppliers, and "Y"/"N" entries for these details.

An invoice may have a single line item, or it may have multiple line items. A line item represents a sub-supplier who meets either of the above criteria.

The logic is that IF the "Prime" supplier (as in, the one issuing an invoice for payment), is Diverse (or Illinois), they get credit for the entire amount of the invoice. If they are NOT Diverse, and they have subs on that invoice who ARE Diverse, the Sub gets credit for the amount of the invoice that represents their billed amount. Same criteria for Illinois.

The problem I'm having is how do I search just within the subset of rows that represent 1 invoice?

Example:
Inv#:1
Supplier: My Company
Prime: Y
Diverse: N
Total Invoice: $100.00
Line Item: $50.00

Inv#:1
Supplier: Joe'sCo.
Prime:N
Diverse:Y
Line Item: $25.00

Inv#:1
Supplier: Jay'sCo
Prime: N
Diverse: Y
Line Item: $25.00

(so in this example, My Company doesn't get any credit for diverse, since they are not. Joe's Co and Jay's Co each get $25.00 in the diverse spend column...

BUT--if My Company was diverse, My Company would get $100 in diverse, and Joe's Co and Jay's Co would get $0.)

Here's a screenshot. I'm attaching the file too. It does have a macro for the combo box, fyi.

If anyone has any ideas, I'm all ears! Thanks.

diversespend.JPG