| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF across multiple worksheets
I need to add up the number of times there is a "Y" in a cell on a
multi-worksheet questionnaire (one sheet per respondant). COUNTIF doesn't seem to work across multiple sheets. Is there a way around this (without resorting to VBA)? I'm thinking I might have to double up on each sheet, convert the Ys to 1s and sum them. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF across multiple worksheets
=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!B1:B10") ,"Y"))
where C1:C3 is a range housing the relevant sheetnames in separate cells, and B1:B10 is the range being checked. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... I need to add up the number of times there is a "Y" in a cell on a multi-worksheet questionnaire (one sheet per respondant). COUNTIF doesn't seem to work across multiple sheets. Is there a way around this (without resorting to VBA)? I'm thinking I might have to double up on each sheet, convert the Ys to 1s and sum them. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF across multiple worksheets
OK - I've changed it to
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D11" ),"Y")) so I can just summarise 1 cell at a time. Works beautifully - thanks Bob! It's a shame that the cell references don't update when I cut and paste the formulas - I'll have to change each manually but it's a small price to pay... "Bob Phillips" wrote in message ... =SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!B1:B10") ,"Y")) where C1:C3 is a range housing the relevant sheetnames in separate cells, and B1:B10 is the range being checked. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... I need to add up the number of times there is a "Y" in a cell on a multi-worksheet questionnaire (one sheet per respondant). COUNTIF doesn't seem to work across multiple sheets. Is there a way around this (without resorting to VBA)? I'm thinking I might have to double up on each sheet, convert the Ys to 1s and sum them. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF across multiple worksheets
If you are just going down rows, you could try
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D"&R OW(A11)),"Y")) which will increment -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... OK - I've changed it to =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D11" ),"Y")) so I can just summarise 1 cell at a time. Works beautifully - thanks Bob! It's a shame that the cell references don't update when I cut and paste the formulas - I'll have to change each manually but it's a small price to pay... "Bob Phillips" wrote in message ... =SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!B1:B10") ,"Y")) where C1:C3 is a range housing the relevant sheetnames in separate cells, and B1:B10 is the range being checked. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... I need to add up the number of times there is a "Y" in a cell on a multi-worksheet questionnaire (one sheet per respondant). COUNTIF doesn't seem to work across multiple sheets. Is there a way around this (without resorting to VBA)? I'm thinking I might have to double up on each sheet, convert the Ys to 1s and sum them. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF across multiple worksheets
genius.
Is it the ! that stops the incrementation? Can I use COLUMN in the same way to getthe columns to increment i.e.: =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!COLU MN(D1)"&ROW(A11)),"Y")) "Bob Phillips" wrote in message ... If you are just going down rows, you could try =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D"&R OW(A11)),"Y")) which will increment -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... OK - I've changed it to =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D11" ),"Y")) so I can just summarise 1 cell at a time. Works beautifully - thanks Bob! It's a shame that the cell references don't update when I cut and paste the formulas - I'll have to change each manually but it's a small price to pay... "Bob Phillips" wrote in message ... =SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!B1:B10") ,"Y")) where C1:C3 is a range housing the relevant sheetnames in separate cells, and B1:B10 is the range being checked. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... I need to add up the number of times there is a "Y" in a cell on a multi-worksheet questionnaire (one sheet per respondant). COUNTIF doesn't seem to work across multiple sheets. Is there a way around this (without resorting to VBA)? I'm thinking I might have to double up on each sheet, convert the Ys to 1s and sum them. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF across multiple worksheets
No, it is the text within INDIRECT.
Column is harder, and it is late here. I'll work on it tomorrow, check back then. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... genius. Is it the ! that stops the incrementation? Can I use COLUMN in the same way to getthe columns to increment i.e.: =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!COLU MN(D1)"&ROW(A11)),"Y")) "Bob Phillips" wrote in message ... If you are just going down rows, you could try =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D"&R OW(A11)),"Y")) which will increment -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... OK - I've changed it to =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D11" ),"Y")) so I can just summarise 1 cell at a time. Works beautifully - thanks Bob! It's a shame that the cell references don't update when I cut and paste the formulas - I'll have to change each manually but it's a small price to pay... "Bob Phillips" wrote in message ... =SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!B1:B10") ,"Y")) where C1:C3 is a range housing the relevant sheetnames in separate cells, and B1:B10 is the range being checked. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... I need to add up the number of times there is a "Y" in a cell on a multi-worksheet questionnaire (one sheet per respondant). COUNTIF doesn't seem to work across multiple sheets. Is there a way around this (without resorting to VBA)? I'm thinking I might have to double up on each sheet, convert the Ys to 1s and sum them. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF across multiple worksheets
Just cracked it
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!"&AD DRESS(ROW(D11),COLUMN(D11) ,4)),"Y")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... No, it is the text within INDIRECT. Column is harder, and it is late here. I'll work on it tomorrow, check back then. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... genius. Is it the ! that stops the incrementation? Can I use COLUMN in the same way to getthe columns to increment i.e.: =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!COLU MN(D1)"&ROW(A11)),"Y")) "Bob Phillips" wrote in message ... If you are just going down rows, you could try =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D"&R OW(A11)),"Y")) which will increment -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... OK - I've changed it to =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D11" ),"Y")) so I can just summarise 1 cell at a time. Works beautifully - thanks Bob! It's a shame that the cell references don't update when I cut and paste the formulas - I'll have to change each manually but it's a small price to pay... "Bob Phillips" wrote in message ... =SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!B1:B10") ,"Y")) where C1:C3 is a range housing the relevant sheetnames in separate cells, and B1:B10 is the range being checked. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... I need to add up the number of times there is a "Y" in a cell on a multi-worksheet questionnaire (one sheet per respondant). COUNTIF doesn't seem to work across multiple sheets. Is there a way around this (without resorting to VBA)? I'm thinking I might have to double up on each sheet, convert the Ys to 1s and sum them. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF across multiple worksheets
magic - thanks, Bob.
"Bob Phillips" wrote in message ... Just cracked it =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!"&AD DRESS(ROW(D11),COLUMN(D11) ,4)),"Y")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... No, it is the text within INDIRECT. Column is harder, and it is late here. I'll work on it tomorrow, check back then. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... genius. Is it the ! that stops the incrementation? Can I use COLUMN in the same way to getthe columns to increment i.e.: =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!COLU MN(D1)"&ROW(A11)),"Y")) "Bob Phillips" wrote in message ... If you are just going down rows, you could try =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D"&R OW(A11)),"Y")) which will increment -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... OK - I've changed it to =SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D11" ),"Y")) so I can just summarise 1 cell at a time. Works beautifully - thanks Bob! It's a shame that the cell references don't update when I cut and paste the formulas - I'll have to change each manually but it's a small price to pay... "Bob Phillips" wrote in message ... =SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!B1:B10") ,"Y")) where C1:C3 is a range housing the relevant sheetnames in separate cells, and B1:B10 is the range being checked. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "merlin" wrote in message ... I need to add up the number of times there is a "Y" in a cell on a multi-worksheet questionnaire (one sheet per respondant). COUNTIF doesn't seem to work across multiple sheets. Is there a way around this (without resorting to VBA)? I'm thinking I might have to double up on each sheet, convert the Ys to 1s and sum them. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUM/COUNTIF across multiple worksheets | Excel Worksheet Functions | |||
| Countif in multiple worksheets | New Users to Excel | |||
| Using Countif with multiple worksheets | Excel Worksheet Functions | |||
| Countif multiple worksheets | Excel Worksheet Functions | |||
| Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
