In Excel 2007 the AVERAGEIFS function will easily allow you to specifiy
multiple conditions
In 2003 you need to change you count to exclude the 0. SumProduct works for
this
=SUMIF(Sheet1!B9:B39,"=" &
B10,Sheet1!AE9:AE39)/SUMPRODUCT(--(Sheet1!B9:B39=B10),--(Sheet1!AE9:AE390))
The sumproduct conditions evaluate as arrays and True=1 and False=0. The
two conditions multipled together evalute anything after the date in B10 and
any daily sales equal to 0 as 0 to give the correct count
This site has a much more detailed explanation
http://xldynamic.com/source/xld.SUMPRODUCT.html
They also cover the closed workbook issue, which SUMPRODUCT is supposed to
handle better. You could convert the SUMIF to a SUMPRODUCT and it may take
care of that problem.
I am not really sure about this, beacuse I don't make extensive use of
linked formulas. If you still have a issue with this you should post a new
question (to improve visibility) and perhaps someone else has a better answer.
Hope this helps point you in the right direction.
--
If this helps, please remember to click yes.
"Curtis" wrote:
#Value error when I use this function
When I open up this file and update the contents it returns an value error
unless I also open up the source document. Is there a way around this?
--
ce
"Paul C" wrote:
In Excel 2007 there is a AVERAGEIF function (look it up in help) that should
do the trick
If you are in Excel 2003 this is not available but you can still achieve
your result by doing a conditional sum and dividing by the number of days
like this
=SUMIF(Sheet1!B9:B39,"=" & B10,Sheet1!AE9:AE39)/COUNTIF(Sheet1!B9:B39,"="
& B10)
--
If this helps, please remember to click yes.
"Curtis" wrote:
I have a report sheet where
Cell b10 represent the day of the month
I need a formula that will look to a prior month sheet for the corresponding
day as identified in B10 and average the sum sales to that point
In the source sheet
Column B (rows 9 thru 39) = day of month
Column AE (row 9 thru 39) = sales
--
ce