You can't use either SUMIF or COUNTIF on closed files.

If you're using Excel 2007 then use the AVERAGEIF function.

If you're not using Excel 2007 then you can use SUMPRODUCT.

--
Biff
Microsoft Excel MVP


"Curtis" .(do not spam) wrote in message
...
I am using the following formula to calculate the average sales for a
specific point in time and it works

However when I close the sheet and reopen it and then update the values it
returns and #Value error until I open up the source sheet.

Is there a way to avoid this?


--
ce


"Paul C" wrote:

In Excel 2007 there is a AVERAGEIF function (look it up in help) that
should
do the trick



=SUMIF(Sheet1!B9:B39,"=" &
B10,Sheet1!AE9:AE39)/COUNTIF(Sheet1!B9:B39,"="
& B10)
--
If this helps, please remember to click yes.




--
ce