Hi,

I am trying to find a value between 3 sheets to find a total o numvber of
British person who have accessed the service. The formula i am using is
=COUNTIF('Apr 07:Jun 07'!G27:G48,"British"), but all i am getting is a value
error.

--
Message posted via http://www.officekb.com

Sheetnames cannot contain the ":" character, at least my copy of excel will
not allow me to enter a : in a sheetname..


"chedd via OfficeKB.com" wrote:

Hi,

I am trying to find a value between 3 sheets to find a total o numvber of
British person who have accessed the service. The formula i am using is
=COUNTIF('Apr 07:Jun 07'!G27:G48,"British"), but all i am getting is a value
error.

--
Message posted via http://www.officekb.com

This is not true. A 3D formula can exist like:

=SUM(Sheet1:Sheet3!A1:A3)

This function will happily sum everything in A1:A3 in all 3 sheets.
The problem is that no all function accept 3D references.

To solve the problem of summing the COUNTIF's of 3 sheets, you can use
the following technique. Write the sheetnames in a separate area, e.g.
cells K7:K9. Now you can use the following formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&K7:K9&"'!G27:G48" ),"British"))

HTH
Kostis Vezerides


On Jun 18, 3:27 pm, JMay wrote:
Sheetnames cannot contain the ":" character, at least my copy of excel will
not allow me to enter a : in a sheetname..

"chedd via OfficeKB.com" wrote:
Hi,

I am trying to find a value between 3 sheets to find a total o numvber of
British person who have accessed the service. The formula i am using is
=COUNTIF('Apr 07:Jun 07'!G27:G48,"British"), but all i am getting is a value
error.

--
Message posted viahttp://www.officekb.com