Hi Dan

It should have worked OK.
By all means send me the file and I will take a look.
Send to roger at technologyNOSPAM4u.co.uk

Do the obvious with "at" and remove NOSPAM
--
Regards

Roger Govier


"Dan the Man" wrote in message
...
Hey Roger and Peter!

You are both great. I'm moving forward here. The formulas you offered
(both
Roger's revision, and Pete's) did take in the appropriate tab (Old
Rules
Sample) where my quality assurance data lives, however the numerical
reading
was 0. I tested the formula (I test everything) for functionality, and
set up
it up to offer a numerical value of 4 (four clients "Referred to Tx"
in Row
AN, and within the 2007 date parameters of the formula per Row Z). I'm
sorry
to be a pain. I'd be glad to send someone my spreadsheet, but I didn't
want
to send extra work to anyone beyond this wonderful help you've been
offering.
I'm off for an hour with a client, so I'll check back to see if Roger
or Pete
have responded after my session.

Eternally Greatful,

Dan

"Pete_UK" wrote:

I think Roger put the sheet name in the wrong place in his earlier
posting. Try this:

=SUMPRODUCT((YEAR('New Rules Sample'!
Z4:Z3500)=2007)*(ISNUMBER(FIND({"Referred to Tx","AC
Initiated","Completed Tx-File Closed"},'New Rules Sample'!
AN4:AN3500))))

Hope this helps.

Pete


On Jul 6, 1:24 pm, Dan the Man
wrote:
Hey Roger!

I did do what you suggested as my initial try (smily cut and pasted
your
example), however I got a formula error message. I did accidently
give you
the wrong name for the tab however (very sorry about that), and the
actual
name is "New Rules Sample" (versus New Rules Tab). I tried to fix
that after
I cut and pasted your example, but alas not luck. Here is what I
cut and
pasted:

=SUMPRODUCT(('New Rules Sample'!YEAR(Z4:Z3500)=2007)*
(ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File
Closed"},'New Rules Sample'!AN4:AN3500))))

Using a regular countif( -or- counta( formula worked just fine when
I did
it, but my spreadsheet didn't seem to like the above Array
calculation. Excel
2002 is so tempremental (or possibly just user error on my part,
lol).

Thanks Roger and Have a good Friday!

Dan



"Roger Govier" wrote:
Hi Dan

Why not just copy and paste the formula I gave you?
What you have entered has the wrong syntax.

--
Regards

Roger Govier

"Dan the Man" wrote in
message
...
Thanks Roger. I still seem to be having difficulty. Perhaps I
was
understanding you wrong (or perhaps I haven't had my coffee
yet, lol),
but it
still isn't seeming to work. I am getting the infamous: #value!
error.
Any
additional clarity you could provide would be greatly
appreciated.
Below is
what I did:

=SUMPRODUCT(('New Rules
Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred
to
Tx","AC
Initiated","Completed Tx-File
Closed"},'New Rules Tab'!AN4:AN3500))

Thanks!

Dan

"Roger Govier" wrote:

Hi Dan

Just insert the tab name in front of the data ranges
Because the tab name has spaces, it must be enclosed in single
quotes,
and followed by the usual exclamation mark
'New Rules Tab'!

=SUMPRODUCT(('New Rules Tab'!YEAR(Z4:Z3500)=2007)*
(ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed
Tx-File
Closed"},'New Rules Tab'!AN4:AN3500))))

--
Regards

Roger Govier

"Dan the Man" wrote in
message
...
Roger, could you give me a little more info. I plugged in
your
formula
but it
didn't seem to work. How would I use your formula examples
to
reference
between tabs? For example, I would be putting the data
results from
the "New
Rules Tab" onto my "QA Tab". The array formula examples you
gave
me,
would be
used to do this, but I didn't know what to input to directly
reference
that I
was pulling the data from the "New Rules Tab" and placing it
on the
"QA Tab"
(in the same spreadsheet). I got the other formula from
Andy and
JMB
to
work, but I couldn't make the array examples you provided.
Any
additional
clarity would be greatly appreciated.

Thanks,

Dan

"Roger Govier" wrote:

Hi

Try
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*(ISNUMBER(FIND({ "Referred
to
Tx","AC
Initiated","Completed Tx-File Closed"},AN4:AN3500))))

and
=SUMPRODUCT((TEXT(Z4:Z3500,mmm yy)="Jan
07")*(ISNUMBER(FIND({"Referred
to Tx","AC Initiated","Completed Tx-File
Closed"},AN4:AN3500))))
--
Regards

Roger Govier

"Dan the Man" wrote
in
message
...
Thanks Andy and JMB!!!

That was very kewl! I'm impressed by the power of Excel.
Now
that I
know if
can do this (develop formulas that can reference data
information
from
a
different tab on my spreadsheet), I wonder if it can
handle
something
a
little more complicated like an "array formula" (as much
of my
statistical
data works with arrays, as well as the "countif" and
"counta"
functions).

Two example array formulas I am using (whose numerical
results
I'd
like to
be referenced on a different worksheet tab) are the
following:

REFERENCES DATA BY A SPECIFIC YEAR

=SUM((YEAR(Z4:Z3500)=2007)*(AN4:AN3500={"Referred to
Tx","AC
Initiated","Completed Tx-File Closed"}))

REFERENCES DATA BY A SPECIFIC MONTH IN A YEAR

=SUM(--(TEXT(Z4:Z3500,"mmm yy")="Jan
07"))*(AN4:AN3500={"Referred
to
Tx","AC
Initiated","Completed Tx-File Closed"})

Best,

Dan

"JMB" wrote:

Any excel formula that accepts a range argument should
be able
to
reference
another worksheet.

=COUNTA('New Rules'!A:A) would count the number of
entries in
new
rules
Column A.

=COUNTIF('New Rules'!B:B, "Depression") would count the
number
of
cells
w/"depression" in column B of new rules worksheet.

Easiest to type =COUNTIF( then use your mouse to
click on
the
sheet you
want and select the range (column B in this example).
Excel
will
put
in 'New
Rules'!B:B for you, then finish by typing the ",
"Depression")"
(sans
the
extra quotes I just used). That way excel handles the
single
quote
and
exclamation placement (which is probably what you are
doing
already
to link
worksheets). Can also use that method to reference
other
workbooks
in your
formulae.

"Dan the Man" wrote:

I have a question?

Is there a formula that I can use which references a
different
"tab" on my
spreadsheet.

For example if on sheet 1 (which of named New Rules) I
have
dates
in which
clients were admitted to our treatment program. Also
on sheet
one,
I list
their diagnoses (e.g depression, anxiety, etc).

On sheet 2 (which I nammed QA Data) I want to list
"stats
only".
For
example, cell A1 on the QA Data sheet may list the
total
number
of
clients in
2007, and another cell may list a break out of
diagnoses.
Again,
the raw data
for this information (name of client, admission date,
diagnosis)
is
on sheet
1 (New Rules), but I want to use sheet 2 (QA Data) to
tally
the
various stats
that I desire (a tab dedicated to stas).

I just didn't know if you can write Excel formula on
one
sheet
askinig to
capture information from another sheet. The only thing
I know
how
to do is to
"link data" between sheets, but I don't want to do
that.

Thanks for any suggestions!

Dan- Hide quoted text -

- Show quoted text -