| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to automatically calculate totals within a Y-N-N/A column
|
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to automatically calculate totals within a Y-N-N/A column
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate totals by fields | Excel Discussion (Misc queries) | |||
| Calculate totals | Excel Discussion (Misc queries) | |||
| calculate totals | Excel Discussion (Misc queries) | |||
| Formula needed - automatically calculate session time totals for each user | Excel Worksheet Functions | |||
| Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions | |||
Linear Mode
