Help needed to create a formula

[Steven_TN]

Hello,

I am trying to do the following (and I am totally stuck).

I have a list of suppliers and their respective deliveries they did for us. For each delivery, I have assigned a scale (green, yellow, red), in order to measure how good the delivery was in terms of timing. What I would like to do next is the following: I need to calculate the % of green/yellow/red based on the subtotal number of deliveries of each supplier and NOT based on the total number of deliveries (that is why I added a countif column, although I don't know if that helps)

For example, for AMBU SRL we had a total of 2 deliveries. One was green, the other one was red. I would like to obtain 50% for the green delivery and 50% for the red one. (see picture attached)

Do you guys know a formula that could yield the result I have manually written in the last column? i would then pivot the all thing to have charts etc.

Thank you!

 

[Clive Muchemwa]

Hi Steven_TN

Not too sure if this is what you are looking for but you can try the formula below;

=COUNTIFS(A:A,A2,C:C,C2)/COUNTIF(A:A,A2)

You can insert the formula in cell D2. Basically the formula uses the COUNTIFS function to count the number of rows that agrees to multiple criteria i.e. in our case, it counts the number of rows that contains the supplier and a specific rating. The COUNTIF function at the end counts the number of supplies made by each supplier.

Regards,
Clive Muchemwa

 

[Steven_TN]

thanks! solved!