software

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,quot;MYYYYquot;),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!
quot;Anthonyquot; wrote:

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

quot;Anthonyquot; wrote:

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

Try

=AVERAGE(IF(Stats!A8:A427=--TEXT(D1,quot;MYYYYquot;),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)

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