| 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. |
| 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 | |||
Hybrid Mode
