Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF across multiple sheets
Hi folks,
I'm pulling together the results of multiple questionaires taking the form of a numeric answer (1-4). The responses have been pulled into a master workbook and the all reside between marker tabs ("first" and "last"). Some formulas work OK across the range but not all. e.g =SUM('first:last'!F4) returns a valid answer as do AVERAGE, COUNT and COUNTA However COUNTIF or SUMIF all return #Value errors. It's not formatting or bad input as the error still occurs when there is only 1 data sheet between the marker tabs. I suspect it may need an 'array' type formula but that's not a strong area for me. Can anyone give me a working COUNTIF formula? Many thanks, Giz |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF across multiple sheets
=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!C8"),"va lue"))
where C1:C3 is a range housing the relevant sheetnames in separate cells. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gizmo63" wrote in message ... Hi folks, I'm pulling together the results of multiple questionaires taking the form of a numeric answer (1-4). The responses have been pulled into a master workbook and the all reside between marker tabs ("first" and "last"). Some formulas work OK across the range but not all. e.g =SUM('first:last'!F4) returns a valid answer as do AVERAGE, COUNT and COUNTA However COUNTIF or SUMIF all return #Value errors. It's not formatting or bad input as the error still occurs when there is only 1 data sheet between the marker tabs. I suspect it may need an 'array' type formula but that's not a strong area for me. Can anyone give me a working COUNTIF formula? Many thanks, Giz |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF across multiple sheets
Thanks Bob (again!),
I doubt I would have ever got to that combination. Shame you can't just specifiy "sheet1:sheet26". Maybe Microsoft will add it sometime. Cheers, Giz "Bob Phillips" wrote: =SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!C8"),"va lue")) where C1:C3 is a range housing the relevant sheetnames in separate cells. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gizmo63" wrote in message ... Hi folks, I'm pulling together the results of multiple questionaires taking the form of a numeric answer (1-4). The responses have been pulled into a master workbook and the all reside between marker tabs ("first" and "last"). Some formulas work OK across the range but not all. e.g =SUM('first:last'!F4) returns a valid answer as do AVERAGE, COUNT and COUNTA However COUNTIF or SUMIF all return #Value errors. It's not formatting or bad input as the error still occurs when there is only 1 data sheet between the marker tabs. I suspect it may need an 'array' type formula but that's not a strong area for me. Can anyone give me a working COUNTIF formula? Many thanks, Giz |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF across multiple sheets
I agree, 3D counting/summing is somewhat crippled. I don't think it is any
better in Office 12 either (but I may be wrong<G). -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gizmo63" wrote in message ... Thanks Bob (again!), I doubt I would have ever got to that combination. Shame you can't just specifiy "sheet1:sheet26". Maybe Microsoft will add it sometime. Cheers, Giz "Bob Phillips" wrote: =SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!C8"),"va lue")) where C1:C3 is a range housing the relevant sheetnames in separate cells. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gizmo63" wrote in message ... Hi folks, I'm pulling together the results of multiple questionaires taking the form of a numeric answer (1-4). The responses have been pulled into a master workbook and the all reside between marker tabs ("first" and "last"). Some formulas work OK across the range but not all. e.g =SUM('first:last'!F4) returns a valid answer as do AVERAGE, COUNT and COUNTA However COUNTIF or SUMIF all return #Value errors. It's not formatting or bad input as the error still occurs when there is only 1 data sheet between the marker tabs. I suspect it may need an 'array' type formula but that's not a strong area for me. Can anyone give me a working COUNTIF formula? Many thanks, Giz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening multiple Excel files that contain varied selected sheets | Excel Worksheet Functions | |||
selecting multiple sheets | Excel Worksheet Functions | |||
Can I unhide multiple sheets at once? | Excel Discussion (Misc queries) | |||
editing multiple sheets at once | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |