IF Formula HELP

Hi everyone,

I am trying to work out the best function which could help when I am making a calculation - For example:

2 Type A + 1 Type B + 2 Type C = 100% (This has to be the mix of the product)

I used this as the 1st formula:

=IF(AND(Q36>=2,Z36=1,AB36>=2),(Q36+Z36+AB36)/$P$3,(3-(COUNTIF(Q36,0)+COUNTIF(Z36,0)+COUNTIF(AB36,0)))/5)

However this then doesn't give a fair % if the mix is under - So I tried this:

=IF(AND(Q10>=2,Z10=1,AB10>=2),(Q10+Z10+AB10)/$P$3,(Q10+Z10+AB10)*$P$4)

But if you now if you have for example the following:

0 Type A + 0 Type B + 5 Type C = you still get 100% which is incorrect?!

Please could anyone help with some ideas how to resolve this calculation or any input so that I can get this right - Even if it's not possible?!?!

Thanks so much in advance for any help!!

Adele

example.jpg
Example.xlsx

can you please post a sample workbook for us to work in? as your example is not all that clear.
So if you can add this workbook, and add a row with expected results we can all help out.