formula help please
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 |
formula help please
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 |
formula help please
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 |
formula help please
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 |
All times are GMT +1. The time now is 07:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com