ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   How to automatically calculate totals within a Y-N-N/A column (https://www.excelbanter.com/excel-worksheet-functions/232624-how-automatically-calculate-totals-within-y-n-n-column.html)

Bill638

How to automatically calculate totals within a Y-N-N/A column
 
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.
--
Bill

Don Guillett

How to automatically calculate totals within a Y-N-N/A column
 
I might do this with a macro but you may benefit from looking in the help
index for
COUNTIF

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bill638" wrote in message
...
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.
--
Bill



Jacob Skaria

How to automatically calculate totals within a Y-N-N/A column
 
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
---------------
Jacob Skaria


"Bill638" wrote:

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.
--
Bill


Bill638

How to automatically calculate totals within a Y-N-N/A column
 
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.
--
Bill


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Bill638" wrote:

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.
--
Bill


Bill638

How to automatically calculate totals within a Y-N-N/A column
 
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...
--
Bill


"Bill638" wrote:

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.
--
Bill


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Bill638" wrote:

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.
--
Bill


Don Guillett

How to automatically calculate totals within a Y-N-N/A column
 
You could simply use countif+countif
or a sumproduct formula
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bill638" wrote in message
...
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...
--
Bill


"Bill638" wrote:

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.
--
Bill


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Bill638" wrote:

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.
--
Bill



Bill638

How to automatically calculate totals within a Y-N-N/A column
 
Hey Don....simple for you (I'm learning - slowly). THAT DID IT!! Many
thanks!!!!
--
Bill


"Don Guillett" wrote:

You could simply use countif+countif
or a sumproduct formula
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bill638" wrote in message
...
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...
--
Bill


"Bill638" wrote:

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.
--
Bill


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Bill638" wrote:

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.
--
Bill





All times are GMT +1. The time now is 10:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com