formula help please

[Anthony]

I have this formula thta looks at cells A8:A427 for a date which is the same
as that in cell D1, and then sums all the coresponding cells from cells
S8:S247.
What I want is NOT the total sum of these cells in S8:S247 but the AVERAGE.

My formula I am using is

=AVERAGE(SUMIF(Stats!A8:A427,--TEXT(D1,"MYYYY"),Stats!R8:R427))

Can anybody explain where I am going wrong and/or suggest a new formula
many thanks

[paul]

sumif()/countif(),same conditions
--
paul
remove nospam for email addy!



"Anthony" wrote:

> I have this formula thta looks at cells A8:A427 for a date which is the same
> as that in cell D1, and then sums all the coresponding cells from cells
> S8:S247.
> What I want is NOT the total sum of these cells in S8:S247 but the AVERAGE.
>
> My formula I am using is
>
> =AVERAGE(SUMIF(Stats!A8:A427,--TEXT(D1,"MYYYY"),Stats!R8:R427))
>
> Can anybody explain where I am going wrong and/or suggest a new formula
> many thanks

[Anthony]

Paul
thanks for your reply, but bit new to this and don't understand where to
place your suggested formula, can you poss write if full
thanks

"Anthony" wrote:

> I have this formula thta looks at cells A8:A427 for a date which is the same
> as that in cell D1, and then sums all the coresponding cells from cells
> S8:S247.
> What I want is NOT the total sum of these cells in S8:S247 but the AVERAGE.
>
> My formula I am using is
>
> =AVERAGE(SUMIF(Stats!A8:A427,--TEXT(D1,"MYYYY"),Stats!R8:R427))
>
> Can anybody explain where I am going wrong and/or suggest a new formula
> many thanks

[Bob Phillips]

Try

=AVERAGE(IF(Stats!A8:A427=--TEXT(D1,"MYYYY"),Stats!R8:R427))

this is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Anthony" <[email protected]> wrote in message
news:[email protected]...
> I have this formula thta looks at cells A8:A427 for a date which is the
same
> as that in cell D1, and then sums all the coresponding cells from cells
> S8:S247.
> What I want is NOT the total sum of these cells in S8:S247 but the
AVERAGE.
>
> My formula I am using is
>
> =AVERAGE(SUMIF(Stats!A8:A427,--TEXT(D1,"MYYYY"),Stats!R8:R427))
>
> Can anybody explain where I am going wrong and/or suggest a new formula
> many thanks