How can you do COUNTIF between 3 spreadsheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chedd - 18 Jun 2007 13:15 GMT

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.

Reply to this Message

JMay - 18 Jun 2007 13:27 GMT

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.

Reply to this Message

vezerid - 18 Jun 2007 13:50 GMT

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..
[quoted text clipped - 8 lines]
> > --
> > Message posted viahttp://www.officekb.com

Reply to this Message