COUNTIF across multiple sheets

=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!C8"),"value"))

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" <Gizmo63@discussions.microsoft.com> wrote in message
news:95CD5B17-DF54-4673-8573-8B49D1EB4F7E@microsoft.com...
> 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

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"),"value"))
>
> 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" <Gizmo63@discussions.microsoft.com> wrote in message
> news:95CD5B17-DF54-4673-8573-8B49D1EB4F7E@microsoft.com...
> > 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
>
>
>

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" <Gizmo63@discussions.microsoft.com> wrote in message
news:5EBDCB61-3F3D-44B7-86B8-86B128751B6F@microsoft.com...
> 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"),"value"))
> >
> > 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" <Gizmo63@discussions.microsoft.com> wrote in message
> > news:95CD5B17-DF54-4673-8573-8B49D1EB4F7E@microsoft.com...
> > > 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
> >
> >
> >