Calculate sum from weighed cells

[Rebel17]

I have a couple ask off this. I'm setting up an Audit form. Audit will either be for "Mail Prep" or "Fulfillment", will never be both. Each of those will have their own set of categories. The value for each category will either be "Yes" or "No", which is represented by the drop downs. For "Mail prep", the weight for each category is highlighted in yellow. Example... If Values in E11:E27 are all true, then the Audit value (G5) should be 100. If Values in K11:K23 are all true, then the Audit value (G5) should be 100.

[josephteh]

Maybe, =COUNTIF(E11:E27,"YES")/(COUNTIF(E11:E27,"YES")+COUNTIF(E11:E27,"NO"))*100

[Rebel17]

Definitely a good start! E11 through E27 have different weight behind them (as highlighted in yellow). For instance, if doing the "Mail Prep" Audit and E17 is a "NO", then the score should be 60, not 89.

[JohnTopley]

This ??

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Rebel17]

Def making progress. One last question...both formulas work, but how do I format G5 to reflect both formulas?

[JohnTopley]

If Values in E11:E27 are all true, then the Audit value (G5) should be 100. If Values in K11:K23 are all true, then the Audit value (G5) should be 100.

You tell me as the above suggests only ONE audit is "actioned" at a given point ! If both are 100, then what do you expect in G5?

Please Login or Register  to view this content.

????

[Rebel17]

This works, thank you soo much!!

[Rebel17]

Hi there! Following up on this.... if I wanted to add the "N/A" in addition to the "YES" that are in E11:E27, how would I incorporate that into the formula you gave me? I tried ($E$11:$E$27="YES","N/A") but that didn't work.

[Rebel17]

Hello!

I was given this formula (below) to use to sum weighed categories, works great. I'm just wanting to add "N/A" to the formula. In other words, the formula below is perfect, but in addition to the "YES", I would like "N/A", when chosen on drop down, to be calculated as well. I tried to change formula to ($E$11:$E$27="YES","N/A but that doesn't do the trick. What am I missing?



=SUMPRODUCT(--($E$11:$E$27="YES"),($A$12:$A$28))+SUMPRODUCT(--($K$11:$K$23="YES"),($G$12:$G$24))

MODERATOR COMMENT: I have merged your two threads as the second one is a continuation of the first. Please do not duplicate requests for assistance.

[alansidman]

If the value is N/A then it should have the same value as a "YES"? Just clarifying my understanding.

=SUMPRODUCT(--($E$11:$E$27="YES"),($A$12:$A$28))+SUMPRODUCT(--($K$11:$K$23="YES"),($G$12:$G$24))+SUMPRODUCT(--($E$11:$E$27="N/A"),($A$12:$A$28))+SUMPRODUCT(--($K$11:$K$23="N/A"),($G$12:$G$24)) 

[The_Snook]

Hi Rebel17,

There's no doubt a better solution but this appears to work.

Please Login or Register  to view this content.

I hope this helps,

Snook

[Rebel17]

Works perfectly, thank you soo much!