COUNTIF assistance, factoring in weighting

[ripcorder]

Hi All,

New member here, so plese be gentle

Here is a table that I have created:
Capture.JPG

Each option (compliant, not compliant, potential, out of scope) are selected via dropdowns.

The formula calculating the % result is:

Please Login or Register  to view this content.

This was all fine until i realised that I needed to add another column for each selection.
Capture1.JPG

Now I need to factor in that each column is weighted, which need to be factored into the % result. The picture shows a result of 25% but really, it needs to show 67%.

Does anyone have any ideas on how i can do this?

Your help is much appreciated. Let me know if you need any more info

[Special-K]

You should edit the subject of this post as per the rules, see Point 1

http://www.excelforum.com/forum-rule...rum-rules.html

before a moderator spots it, a knock on effect of this breach is if we try to solve your problem the mods chastise us too!
So change it quickly if you want a speedy response.

[Fotis1991]

You should edit the subject of this post as per the rules, see Point 1

http://www.excelforum.com/forum-rule...rum-rules.html

before a moderator spots it, a knock on effect of this breach is if we try to solve your problem the mods chastise us too!
So change it quickly if you want a speedy response.

That's corect!

ripcorder

Welcome to the forum.

[ripcorder]

Thanks for the heads up! Won't happen again

[Special-K]

It's actually SUMIF

I dont like doing it this way but I cant see another method just yet

=(A1*(B1="Compliant")+C1*(D1="Compliant")+E1*(F1="Compliant")+G1*(H1="Compliant"))/SUM(A1:G1)
and copy down the column


UPDATE: This works

=SUMIF(B1:H1,"Compliant",A1:G1)/SUM(A1:G1)

[ripcorder]

Hi Special k,

Thanks for the quick responce!

The formula you have given works for that set of results, but if the dropdowns changes, the result stays the same? I need it to adapt to the selections in the drop downs as well as the numbers indicationg the weighting.

Capture2.JPG

Thanks again

UPDATE:

IGNORE ME...I'm an idiot!!

Thanks so much for your help!! Much appreciated

[Special-K]

When you set up the dropdowns dont you refer to a cell to store the value of the dropdown?
If so just use that cell reference.

Might be better to upload your file.

[ripcorder]

It's ok...I figured out where I went wrong. I copied your formula into the wrong row, so the cell references weren't right. School boy error!

Thanks again for the help