How to automatically calculate totals within a Y-N-N/A column

[Bill638]

I am creating an audit workbook with a number of worksheets. The primary
worksheet includes questions and a column for the auditor to select Yes, No
or N/A. I'd like to create a worksheet that "scores" the facility visited. In
order to do that, I need to count the number of No and N/A responses
automatically. To make it a bit more challenging, there are nine distinct
sections within the primary audit worksheet, and I want to score each section
separately and then take the total of each of those, add them together and
create a total score. First, I need a formula to automatically calculate the
total of No's and N/A's in each section, then a formula to add all of those
together, and lastly a formula to capture that data on the scoring page and
automatically apply it to the cells on that page that total those numbers by
section and total report (clear as mud, right?). I'd greatly appreciate any
help you cam provide. Thanks.

 

[Jacob Skaria]

Bill

To count number of 'Yes' from Sheet 'Scores' Column A use the below formula

=COUNTIF(Scores!A:A,"Yes")

In your scoring page apply the below formula for "No" and "N/A"


If this post helps click Yes

 

[Bill638]

Hi Jacob - thanks for the quick response, but there is no formula below your
"In your scoring page apply the below formula for "No" and "N/A""

Also, as I indicated earlier, I have nine parts to the audit and I'd like to
score each part, so I'll need to insert beginning and ending row numbers into
the first formula (and the second one also), right.

Please let me know how I configure the "Yes" formula to take the rows into
account and also the formula for the scoring page - and I'll try everything
you recommend and let you know.

Thanks again.

 

[Bill638]

Jacob - OK, I've got the formula down to count the number of No answers in
the column and have figured out how to separate each part - but I can't come
up with the formula to count both NO and N/A answers together.

The I went to the scoring worksheet and tried to just copy those cell totals
from the Checklist worksheet, but I couldn't come up with the correct
formula. Then it dawned on me - is it possible to put formulas on the scoring
page (one for each part and then one that totals all parts) that simply draws
the basic NO-N/A data directly from the Checklist worksheet and save a step?

Again, I really appreciate your time and expertise.

Thanks...

 

[Don Guillett]

You could simply use countif+countif
or a sumproduct formula

 

[Bill638]

Hey Don....simple for you (I'm learning - slowly). THAT DID IT!! Many
thanks!!!!