Attempting to highlight transactions across Vendors/Areas/PO Numbers with SUMPRODUCT

Hi,

I have a large data set on sheet 1 (circa 25,000 lines) with columns for Vendor/PO/Area and $ Spend. I'm trying to setup a formula in sheet 2 to highlight all instances where a vendor has been used, by the same area with a spend of > $20k and where multiple purchases orders have been utilised (2 or more).

I know that I could achieve this by using pivots, but ideally I'm trying to make this dynamic as other users copy in additional data.

It sounds like a job for COUNTIFS/SUMIFS or SUMPRODUCT I think, but I've no idea how to put it together. If possible I don't want the end user to to populate the column of vendors in sheet 2.

I suppose if these isn't a dynamic formula I could create a table with vendors and areas and SUM spend with multiple PO's using COUNT/FREQUENCY.

Any help appreciated and I've attached an example of the dataCheck Example.xlsx

Thanks

Dave

Hi nathansav,

I'm looking to highlight/list dynamically where the same vendor has been used by the same area with two or more purchase orders, and where the usage amounts to more than $20k.

In the attached example on sheet 1 Vendor 1 has been used by Area 1 and Area 2, in both cases 2 PO numbers have been used (which is important as if it was the same PO for each area it wouldn't count)

In the cases above the transactions total over $20k for each instance.

Apologies if I'm not explaining that well.

Thanks nflsales,

That's a fantastic solution, very much appreciated.

I'll close the thread :-)