Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Number of Occurrences in worksheets
I have a project that entails calculating the number of times an answer
appears on a range of worksheets and inserting the result on my compilation worksheet in the same file. For example: Question Yes No Other Do you like Sushi? X I need to calculate the total number of "X" in the Yes, No and Other columns from many separate worksheets in the same file. I am using Excel 2002. Any assistance would be appreciated. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Number of Occurrences in worksheets
With the sheetnames in range G1:G10; the below formula will return the count
of X in cell B2 in all sheets mentioned in the range G1:G10 =SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X")) If this post helps click Yes --------------- Jacob Skaria "Terri" wrote: I have a project that entails calculating the number of times an answer appears on a range of worksheets and inserting the result on my compilation worksheet in the same file. For example: Question Yes No Other Do you like Sushi? X I need to calculate the total number of "X" in the Yes, No and Other columns from many separate worksheets in the same file. I am using Excel 2002. Any assistance would be appreciated. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Number of Occurrences in worksheets
Unfortunately, I receive a "#NAME?" error when I try this formula. I tried
it with and without spaces between the & and what follows. Not sure what I'm doing wrong. Thanks. "Jacob Skaria" wrote: With the sheetnames in range G1:G10; the below formula will return the count of X in cell B2 in all sheets mentioned in the range G1:G10 =SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X")) If this post helps click Yes --------------- Jacob Skaria "Terri" wrote: I have a project that entails calculating the number of times an answer appears on a range of worksheets and inserting the result on my compilation worksheet in the same file. For example: Question Yes No Other Do you like Sushi? X I need to calculate the total number of "X" in the Yes, No and Other columns from many separate worksheets in the same file. I am using Excel 2002. Any assistance would be appreciated. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Number of Occurrences in worksheets
The only way I can see you getting a #Name? error is misspelling a function
or a cell reference, or if your actual formula is using ranges that may be non-existent or misspelled. Did you copy Jacob's formula directly into your sheet from his post? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Terri" wrote in message ... Unfortunately, I receive a "#NAME?" error when I try this formula. I tried it with and without spaces between the & and what follows. Not sure what I'm doing wrong. Thanks. "Jacob Skaria" wrote: With the sheetnames in range G1:G10; the below formula will return the count of X in cell B2 in all sheets mentioned in the range G1:G10 =SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X")) If this post helps click Yes --------------- Jacob Skaria "Terri" wrote: I have a project that entails calculating the number of times an answer appears on a range of worksheets and inserting the result on my compilation worksheet in the same file. For example: Question Yes No Other Do you like Sushi? X I need to calculate the total number of "X" in the Yes, No and Other columns from many separate worksheets in the same file. I am using Excel 2002. Any assistance would be appreciated. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Number of Occurrences in worksheets
Yes, but will try again.
"RagDyer" wrote: The only way I can see you getting a #Name? error is misspelling a function or a cell reference, or if your actual formula is using ranges that may be non-existent or misspelled. Did you copy Jacob's formula directly into your sheet from his post? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Terri" wrote in message ... Unfortunately, I receive a "#NAME?" error when I try this formula. I tried it with and without spaces between the & and what follows. Not sure what I'm doing wrong. Thanks. "Jacob Skaria" wrote: With the sheetnames in range G1:G10; the below formula will return the count of X in cell B2 in all sheets mentioned in the range G1:G10 =SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X")) If this post helps click Yes --------------- Jacob Skaria "Terri" wrote: I have a project that entails calculating the number of times an answer appears on a range of worksheets and inserting the result on my compilation worksheet in the same file. For example: Question Yes No Other Do you like Sushi? X I need to calculate the total number of "X" in the Yes, No and Other columns from many separate worksheets in the same file. I am using Excel 2002. Any assistance would be appreciated. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Number of Occurrences in worksheets
Hi Teri
G1:G10 range should not be blank or invalid. Try with 3 sheets.. =SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G3 &"'!b2"),"X")) If this post helps click Yes --------------- Jacob Skaria "Terri" wrote: Yes, but will try again. "RagDyer" wrote: The only way I can see you getting a #Name? error is misspelling a function or a cell reference, or if your actual formula is using ranges that may be non-existent or misspelled. Did you copy Jacob's formula directly into your sheet from his post? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Terri" wrote in message ... Unfortunately, I receive a "#NAME?" error when I try this formula. I tried it with and without spaces between the & and what follows. Not sure what I'm doing wrong. Thanks. "Jacob Skaria" wrote: With the sheetnames in range G1:G10; the below formula will return the count of X in cell B2 in all sheets mentioned in the range G1:G10 =SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X")) If this post helps click Yes --------------- Jacob Skaria "Terri" wrote: I have a project that entails calculating the number of times an answer appears on a range of worksheets and inserting the result on my compilation worksheet in the same file. For example: Question Yes No Other Do you like Sushi? X I need to calculate the total number of "X" in the Yes, No and Other columns from many separate worksheets in the same file. I am using Excel 2002. Any assistance would be appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of occurrences... | Excel Worksheet Functions | |||
counting occurrences | Excel Discussion (Misc queries) | |||
Counting occurrences | Excel Discussion (Misc queries) | |||
Counting number of occurrences. | Excel Worksheet Functions | |||
Counting number of occurrences | Excel Worksheet Functions |