Hello.

I have a list of part numbers (appr. 8000 rows) and on some parts there are shortages in our warehouse.

I am using this function to find how many shortages there are all together:
=SUMPRODUCT((MaterialController=$A7)*(Demand>0)*((Stock<Demand)))
This works very well and gives me how many shortages the current Material Controller in cell A7 have.

The Material controller have a number of Suppliers connected to him. I would like to summarize how the shortages are divided between all the suppliers connected to the current material controller.

Lets say that the current material controller have 10 suppliers and only 5 of them have shortages. How can I get that only 5 suppliers are affected from my part list? I have a defined range called SupplierNumber where all suppliers are listed by part number.

The table contains these column names:
PartNumber
MaterialController
Demand
Stock
SupplierNumber

Does anyone have any suggestions how I can get this information with a simple function?

If I have not been precise, please ask and I will contribute with more information.

/Anders