+ Reply to Thread
Results 1 to 4 of 4

COUNTIF across multiple sheets

  1. #1
    Gizmo63
    Guest

    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. #2
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Gizmo63
    Guest

    Re: 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"),"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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #4
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1