Hi guys,

I start with a simple COUNTIF formula that is like this:

=COUNTIF(Germany!$B$6:$BK$6,"22.12.2004")

Then, I would like to make it more flexible, as there can be multiple
worksheets and multiple dates to request. The parts that I would like to
refer to indirectly would be country's name (f. ex. "Germany") and the
specific date (f. ex. 22.12.2004). The formula would be implemented on the
worksheet named "Totals".

Intuitively, I thought that the new formula should be more or less like the
one just below, but for some reason it's not working...

=COUNTIF(INDIRECT(B1&"!$B$6:$BK$6,Totals!"&A2))

(FYI: In the cell B1 I would put worksheets' name (f. ex. Germany) and in A2
would put the date I'm interested in (f. ex. 22.12.2004))

Any idea what I'm doing wrong?
Thanks for your help!

Gizmo

INDIRECT returns a range, not just a string. Try:

=COUNTIF(INDIRECT(B1&"!$B$6:$BK$6"),Totals!A2)



In article ,
"Gizmo" wrote:

Hi guys,

I start with a simple COUNTIF formula that is like this:

=COUNTIF(Germany!$B$6:$BK$6,"22.12.2004")

Then, I would like to make it more flexible, as there can be multiple
worksheets and multiple dates to request. The parts that I would like to
refer to indirectly would be country's name (f. ex. "Germany") and the
specific date (f. ex. 22.12.2004). The formula would be implemented on the
worksheet named "Totals".

Intuitively, I thought that the new formula should be more or less like the
one just below, but for some reason it's not working...

=COUNTIF(INDIRECT(B1&"!$B$6:$BK$6,Totals!"&A2))

(FYI: In the cell B1 I would put worksheets' name (f. ex. Germany) and in A2
would put the date I'm interested in (f. ex. 22.12.2004))

Any idea what I'm doing wrong?
Thanks for your help!

Gizmo

Hi
try
=COUNTIF(INDIRECT("'" & B1 &"'!B6:BK6"),C1)
B1: country name
C1: date value

--
Regards
Frank Kabel
Frankfurt, Germany
"Gizmo" schrieb im Newsbeitrag
...
Hi guys,

I start with a simple COUNTIF formula that is like this:

=COUNTIF(Germany!$B$6:$BK$6,"22.12.2004")

Then, I would like to make it more flexible, as there can be multiple
worksheets and multiple dates to request. The parts that I would like to
refer to indirectly would be country's name (f. ex. "Germany") and the
specific date (f. ex. 22.12.2004). The formula would be implemented on the
worksheet named "Totals".

Intuitively, I thought that the new formula should be more or less like
the
one just below, but for some reason it's not working...

=COUNTIF(INDIRECT(B1&"!$B$6:$BK$6,Totals!"&A2))

(FYI: In the cell B1 I would put worksheets' name (f. ex. Germany) and in
A2
would put the date I'm interested in (f. ex. 22.12.2004))

Any idea what I'm doing wrong?
Thanks for your help!

Gizmo

Thanks guys for your valuable comments!
Now I see much clearer...

Gizmo

"Frank Kabel" wrote in message
...
Hi
try
=COUNTIF(INDIRECT("'" & B1 &"'!B6:BK6"),C1)
B1: country name
C1: date value

--
Regards
Frank Kabel
Frankfurt, Germany
"Gizmo" schrieb im Newsbeitrag
...
Hi guys,

I start with a simple COUNTIF formula that is like this:

=COUNTIF(Germany!$B$6:$BK$6,"22.12.2004")

Then, I would like to make it more flexible, as there can be multiple
worksheets and multiple dates to request. The parts that I would like to
refer to indirectly would be country's name (f. ex. "Germany") and the
specific date (f. ex. 22.12.2004). The formula would be implemented on
the
worksheet named "Totals".

Intuitively, I thought that the new formula should be more or less like
the
one just below, but for some reason it's not working...

=COUNTIF(INDIRECT(B1&"!$B$6:$BK$6,Totals!"&A2))

(FYI: In the cell B1 I would put worksheets' name (f. ex. Germany) and
in
A2
would put the date I'm interested in (f. ex. 22.12.2004))

Any idea what I'm doing wrong?
Thanks for your help!

Gizmo

Hi Gizmo,
Certainly don't want to discourage thanking responders in a
thread or in email, but it is best not to change the subject,

It is hard to follow a thread in Google Groups when the subject
changes. It also tends to confuse -- is it the same thread or not.

And it is important in some threads to indicate which solution was
tried/worked in those that have conflicting suggestions, a change
of subject may make the thank you look like it applies to something else.

Most of the people answering will see replies to their answers,
usually in RED and by using Outlook Express. Without the quoted
material you included, one might have to switch to viewing all
messages just to tell which question was being responded to as
many responders answer more than one question in day though
there are few days that anyone surpasses Frank in number of
responses.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"Gizmo" wrote ...
Thanks guys for your valuable comments!
Now I see much clearer...

"Frank Kabel" wrote in message
try
=COUNTIF(INDIRECT("'" & B1 &"'!B6:BK6"),C1)
B1: country name
C1: date value