Average with Conditions

[Curtis]

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?

 

[T. Valko]

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.

 

[Curtis]

SUMPRODUCT seems to work so I changed the formula to

=(SUMPRODUCT(--('[0910-05-Sales Summary
Report.xlsx]BST'!$B$9:$B$39<=$D10)*('[0910-05-Sales Summary
Report.xlsx]BST'!$AE$9:$AE$39)))/$D10

Question is that one of the limitations of SUMIF??

Thanks

 

[T. Valko]

Question is that one of the limitations of SUMIF??

SUMIF won't work on a closed file.

--
Biff
Microsoft Excel MVP

SUMPRODUCT seems to work so I changed the formula to

=(SUMPRODUCT(--('[0910-05-Sales Summary
Report.xlsx]BST'!$B$9:$B$39<=$D10)*('[0910-05-Sales Summary
Report.xlsx]BST'!$AE$9:$AE$39)))/$D10

Question is that one of the limitations of SUMIF??

Thanks
--
ce

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?