Formula for auditing tool

Question

Hello,

I am creating an auditing tool with 5 categories to be scored. I am trying to create a formula that will let me add the sum for the row of the audit, however divide by the "possible score" and multiply by 10. I want to add a condition that if 1 cell is left blank that it will not be included in the sum so the score will not be lowered.

Is this possible to do? I tried a SUMIF =(SUMIF(B5:F5,"<>"&"")/SUM($B$3:$F$3))*10 formula but it was still dividing like there was an option to score in the category.

Example: You can score a 10 all together, however if 1 category was not audited, Cell E3(or the one that was not used) will be blank, therefore the possible score will not be picked up in our range.

Thanks!

Answer 1

I think this is what you actually want:

=SUM(B5:F5)/SUMIF(B5:F5,"<>"&"",$B$3:$F$3)*10

For scoring purposes, it is handy to use a weighting function:

=SUMPRODUCT(B5:F5,$B$3:$F$3)/SUMIF(B5:F5,"<>"&"",$B$3:$F$3)*10

Where B3:F3 has relative weights for the factors (a weight of 2 is twice the weighting as a 1, but half that of a 4, for example), and B5:F5 have scores that are, say, 1 to 10 or blank.

Answer 2

Thank you Bernie! This got me the results I was looking for! I am also happy to see that I was at least going in the right direction with my formula.

Thanks again!