Determining Values Based on Multiple Criteria

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

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 6)