Thanks.

In sheet 1, cell V3 I have the formula =COUNTIF(I2:I10000,"o2") (result = 18)
I want this result to end up in cell b3 of sheet 5.
So, if cell b3 on sheet 5 I've entered =sheet1!V3 ( but excel makes me
select a source file, even though it's the same workbook) so it ends up as
=[sheet1]Sheet1!V3.
This returns the result 0 (not 18).

What am I doing wrong?

With regard to array formulas I've a new question.
I want to count the incidences when "o2" appears in column J at the same
time as "injection" appears in column e, f or g.

I've tried
{=((COUNTIF(E2:G10000,"injection")*(J2:J10000="O1" )))} but this doesn't seem
to work. Can you help?

Thanks.

Paul


"T. Valko" wrote:

Just use simple links:

=Sheet1!A1

--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
Thanks. I've got it working.

On a seperate issue (if you don't mind)

I have a workbook with 4 worksheets.
Each worksheet contains a section with formulas summarising the data on
that
worksheet.

I'd like to copy these summary tables onto a 5th sheet so they can be read
easily. If possible I'd like to maintain the formulas so that if I make a
change to the data on sheet 1 it is updated in the summary on sheet 5.

Any help would be much appreciated.

Paul



"T. Valko" wrote:

I'd entered a bigger range of cells than actually have
numbers in order to anticipate future data entry

You can do that. You just can't use entire column references unless
you're
using Excel 2007.

--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
Thanks again, I've got it working. I'd entered a bigger range of
cells
than
actually have numbers in order to anticipate future data entry and this
seemed to be the problem.
I've changed to the appropriate number of cells and it seems to have
worked.

Thanks so much for your help,

Paul




"T. Valko" wrote:

See this screencap:

http://img154.imageshack.us/img154/6089/avgifwe2.jpg

As you'll see, the formula returns the correct result.

get the result #N/A.

Do you have any #N/A errors in any of your ranges? If so, that's why
you're
getting that result.


--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
Yes

=AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0))

I have copied this from the cell.
y2 is a cell containing the number 70
y3 is a cell containing the word midazolm
The N column contains the numbers I want to average.

Thanks



"Bob Phillips" wrote:

Did you array enter it as shown?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"Paul" wrote in message
...
I've tried the formulaes as you suggested but get the result
#N/A.
I can't get the averageifs to work at all (but I have office
2003).

Any suggestions?

Thanks,

Paul

"T. Valko" wrote:

Try one of these:

This array formula** works in all versions of Excel :

=AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15))

Or, this normally entered formula in Excel 2007 only:

=AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X")

Better to use cells to hold the criteria:

A1 = 70
B1 = X

=AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15))

=AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1)

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
I need to averge based on several criteria.

In column C I have ages
In column M I have words for medication used by a patient
In column N I have numbers

I'd like to search column C for certain ages and then M for a
drug
and
then
average the dose (in column N) for those meeting criteria for
both
colums
C
and M

i.e. average dose (column N) for people under 70 (column C) on
drug
x
(column M).

I'd really appreciate any help.

Paul