How do I calculate external values from a closed worksheet

[philstu]

I have just upgraded to Excel 2007 am finding that cells are not
recalculating from values in closed worksheets as they did in version 2002.
While in the open worksheet, I press F2 and then Enter, and it tells me it
can't find the closed worksheet, then I click the file name and it updates
the cell, however I have to do this for every cell that has a link to a
closed worksheet. I have checked and rechecked the formula in my open
worksheet several times and it is typed correctly. Do I have to go through
this every time? Or is there some way to get the open worksheet to find the
closed worksheet and update the values in the cells of the open worksheet?

 

[Sean Timmons]

This typically happens with countif and sumif. If you use sumproduct, your
issues should go away...

If column A is numeric and column B is text:

To use sumproduct instead of countif:

=countif ('Sheet1'!A:A,A2)

=sumproduct(--('Sheet1'!A2:A10000=A2),--('Sheet1'!B2:B10000))

And to do sumif:

=sumproduct(--('Sheet1'!A2:A10000=A2))

Replace 'Sheet1' with your workbook and sheet name...