How can you do COUNTIF between 3 spreadsheets

[chedd via OfficeKB.com]

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.

 

[Guest]

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

 

[vezerid]

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

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

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.