SUMIFS

[rprasanthkumar]

Dear I am using Excel 2010 and tried out the SUMIFS. It was all perfect as it produced the results what I wanted.


But the story is now a bit anticlimax. The moment I close the base file from which the sumifs formulae picks up the data, all the cells in the output file shows as "Values".


This error was not coming when we use sumif function. Why is this happening? Is there any way we can fix this error from appearing?

 

[Luke M]

Technically, the SUMIF will have trouble as well. It simply hasn't tried to recalculate yet.

SUMIF and SUMIFS won't work on a closed external workbook. My recommendation would be to use some helper cells/sheet to do a regular link to other workbook

E.g.

='[Other Workbook.xls]Sheet1!A1


And then do a sumif on that.

 

[rprasanthkumar]

So that means all that is going to be there in the base sheet needs to be replicated into one of the sheet in the current file also a link sheet? Does it not increase the size of the file? Because there will be multiple files created right?

 

[Hui]

Are you saving as *.xlsx/xlsb or xlsm? Or as *.xls/m ?

 

[Luke M]

Yes to first 2 questions. I don't understand why there will be multiple files created however.


New idea, it'll take more work, but you might want to consider setting up a query using the other worksheet as an external data source. You could setup the query to essentially do the SUMIF. The query wizard was simply "ok" in 2003, but hopefully it has been improved in 2007 or 2010. Anyway, you tell the query where to look, and then you can set which columns you want returned. You can even perform math operations (eg, return Sum of Sales column). You can then set criteria (eg, Date is greater than 3/1/2011). Criteria can even use parameters that are defined in your workbook.


Queries work similar to PivotTables in that you need to refresh them.