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

=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

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

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