Average with Conditions

  • Thread starter Thread starter Curtis
  • Start date Start date
C

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?
 
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.
 
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
 
Question is that one of the limitations of SUMIF??

SUMIF won't work on a closed file.

--
Biff
Microsoft Excel MVP


Curtis said:
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


Curtis said:
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?
Click to expand...
Click to expand...
 
Back
Top