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

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

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

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" wrote in message
...
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