shortages divided by suppliers

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

Hi,

Have you tried a simple pivot table ?

Carim

Hello Carim

That would work but the way I am presenting the results are not possible to use the pivot table on.

Too bad though.

/Ander

Hello again.

I found this very usefule code on the net:
=SUM(IF(FREQUENCY(SupplierNumber;SupplierNumber)>0;1))

This counts all unique supplier numbers and returns the quantity of suppliers connected to the parts in my list.

How can I put this in my function:
=SUMPRODUCT((MaterialController=$A7)*(Demand>0)*(( Stock<Demand)))

To only get unique suppliers to the current material controller?

/Anders