Home |
Search |
Today's Posts |
#1
|
|||
|
|||
countif across worksheets
I am trying to automate ta summary page of a quesionnaire process. The
questionnaire contains 6 worksheets. 5 of them are the questionnaires and the 6th is the summary sheet. On the questionnaires there are 3 columns: yes, no and n/a. On the summary sheet I need to count the values in the yes column and turn that into a percentage. The value placed in the field is an "X", and the top score would be 100% (5/5). At this point I am trying just to count the X's in the yes column. I am trying to use countif and listing the range as the cell in each worksheet, separated by a comma, then "X". It is not working and gives me an error - can't figure out why, nor how to fix it. Does anyone have any suggestions? Thanks in advance for your help. |
#2
|
|||
|
|||
I think that you will have to add a separate sumif for each sheet
"MCK" wrote: I am trying to automate ta summary page of a quesionnaire process. The questionnaire contains 6 worksheets. 5 of them are the questionnaires and the 6th is the summary sheet. On the questionnaires there are 3 columns: yes, no and n/a. On the summary sheet I need to count the values in the yes column and turn that into a percentage. The value placed in the field is an "X", and the top score would be 100% (5/5). At this point I am trying just to count the X's in the yes column. I am trying to use countif and listing the range as the cell in each worksheet, separated by a comma, then "X". It is not working and gives me an error - can't figure out why, nor how to fix it. Does anyone have any suggestions? Thanks in advance for your help. |
#3
|
|||
|
|||
I'm not sure I understand. Can you explain what you mean, please? Thanks.
"bj" wrote: I think that you will have to add a separate sumif for each sheet "MCK" wrote: I am trying to automate ta summary page of a quesionnaire process. The questionnaire contains 6 worksheets. 5 of them are the questionnaires and the 6th is the summary sheet. On the questionnaires there are 3 columns: yes, no and n/a. On the summary sheet I need to count the values in the yes column and turn that into a percentage. The value placed in the field is an "X", and the top score would be 100% (5/5). At this point I am trying just to count the X's in the yes column. I am trying to use countif and listing the range as the cell in each worksheet, separated by a comma, then "X". It is not working and gives me an error - can't figure out why, nor how to fix it. Does anyone have any suggestions? Thanks in advance for your help. |
#4
|
|||
|
|||
There may be an easier way, but this will work.
=(COUNTIF(Sheet1!A:A,"x")+(COUNTIF(Sheet2!A:A,"x") +(COUNTIF(Sheet3!A:A,"x")+(COUNTIF(Sheet4!A:A,"x") +(COUNTIF(Sheet5!A:A,"x")))))) A:A is your yes column. HTH, Paul "MCK" wrote in message ... I am trying to automate ta summary page of a quesionnaire process. The questionnaire contains 6 worksheets. 5 of them are the questionnaires and the 6th is the summary sheet. On the questionnaires there are 3 columns: yes, no and n/a. On the summary sheet I need to count the values in the yes column and turn that into a percentage. The value placed in the field is an "X", and the top score would be 100% (5/5). At this point I am trying just to count the X's in the yes column. I am trying to use countif and listing the range as the cell in each worksheet, separated by a comma, then "X". It is not working and gives me an error - can't figure out why, nor how to fix it. Does anyone have any suggestions? Thanks in advance for your help. |
#5
|
|||
|
|||
Try...
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2 ","Sheet3","Sheet4","Sh eet5"}&"'!A2:A10"),"x")) or =SUMPRODUCT(COUNTIF(INDIRECT("'"&$D$1:$D$5&"'!A2:A 10"),"x")) ....where D1:D5 contains your sheet names. You can also use a whole column reference, if you wish... =SUMPRODUCT(COUNTIF(INDIRECT("'"&$D$1:$D$5&"'!A:A" ),"x")) Hope this helps! In article , MCK wrote: I am trying to automate ta summary page of a quesionnaire process. The questionnaire contains 6 worksheets. 5 of them are the questionnaires and the 6th is the summary sheet. On the questionnaires there are 3 columns: yes, no and n/a. On the summary sheet I need to count the values in the yes column and turn that into a percentage. The value placed in the field is an "X", and the top score would be 100% (5/5). At this point I am trying just to count the X's in the yes column. I am trying to use countif and listing the range as the cell in each worksheet, separated by a comma, then "X". It is not working and gives me an error - can't figure out why, nor how to fix it. Does anyone have any suggestions? Thanks in advance for your help. |
#6
|
|||
|
|||
One way
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$Z$1:$Z$10&"'!A1" ),"Y"))where the criteria is "Y", note that you need to put all sheet names in a range (in this caseZ1:Z10 hold the sheet names) and the formula checks A1 in all the sheets, you can us a range there as well=SUMPRODUCT(COUNTIF(INDIRECT("'"&$Z$1:$Z$10&"' !A1:A10"),"Y")) -- Regards, Peo Sjoblom (No private emails please) "MCK" wrote in message ... I'm not sure I understand. Can you explain what you mean, please? Thanks. "bj" wrote: I think that you will have to add a separate sumif for each sheet "MCK" wrote: I am trying to automate ta summary page of a quesionnaire process. The questionnaire contains 6 worksheets. 5 of them are the questionnaires and the 6th is the summary sheet. On the questionnaires there are 3 columns: yes, no and n/a. On the summary sheet I need to count the values in the yes column and turn that into a percentage. The value placed in the field is an "X", and the top score would be 100% (5/5). At this point I am trying just to count the X's in the yes column. I am trying to use countif and listing the range as the cell in each worksheet, separated by a comma, then "X". It is not working and gives me an error - can't figure out why, nor how to fix it. Does anyone have any suggestions? Thanks in advance for your help. |
#7
|
|||
|
|||
Assuming that the yes answers are in E1:E100, try
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2 ","Sheet3"","Sheet4","Shee t5"}&"'!E1:E10"),"X")) etc. -- HTH RP (remove nothere from the email address if mailing direct) "MCK" wrote in message ... I am trying to automate ta summary page of a quesionnaire process. The questionnaire contains 6 worksheets. 5 of them are the questionnaires and the 6th is the summary sheet. On the questionnaires there are 3 columns: yes, no and n/a. On the summary sheet I need to count the values in the yes column and turn that into a percentage. The value placed in the field is an "X", and the top score would be 100% (5/5). At this point I am trying just to count the X's in the yes column. I am trying to use countif and listing the range as the cell in each worksheet, separated by a comma, then "X". It is not working and gives me an error - can't figure out why, nor how to fix it. Does anyone have any suggestions? Thanks in advance for your help. |
#8
|
|||
|
|||
I tried this one, and did get this to work. Thank you all for your help.
"PCLIVE" wrote: There may be an easier way, but this will work. =(COUNTIF(Sheet1!A:A,"x")+(COUNTIF(Sheet2!A:A,"x") +(COUNTIF(Sheet3!A:A,"x")+(COUNTIF(Sheet4!A:A,"x") +(COUNTIF(Sheet5!A:A,"x")))))) A:A is your yes column. HTH, Paul "MCK" wrote in message ... I am trying to automate ta summary page of a quesionnaire process. The questionnaire contains 6 worksheets. 5 of them are the questionnaires and the 6th is the summary sheet. On the questionnaires there are 3 columns: yes, no and n/a. On the summary sheet I need to count the values in the yes column and turn that into a percentage. The value placed in the field is an "X", and the top score would be 100% (5/5). At this point I am trying just to count the X's in the yes column. I am trying to use countif and listing the range as the cell in each worksheet, separated by a comma, then "X". It is not working and gives me an error - can't figure out why, nor how to fix it. Does anyone have any suggestions? Thanks in advance for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Countif multiple worksheets | Excel Worksheet Functions | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
"countif" from multiple worksheets within workbook | Excel Discussion (Misc queries) |