Forum Discussion

TFS1990's avatar
TFS1990
Copper Contributor
Sep 28, 2023
Solved

SUMIFS across closed workbooks

Hello....I am SUMIFS but I am wanting to run across two workbooks (one of which would be closed).
SUMIFS will copy across but does not work.
Some help needed to get this one going?
Thanks 🙂

  • TFS1990 I would suggest to pull the relevant table into a separate worksheet using Data, Get Data, From File, From workbook. Then have your SUMIFS refer to that new table. You can set the new table to auto-refresh when the file opens.

4 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor

    TFS1990 SUMIFS doesn't work on a closed file, period. A possible solution is to use PowerQuery (Data, Get Data, From File, From workbook) to fetch the data from wb2 into wb1 (on a separate tab) and then use the separate tab to do the SUMIFS against.

    • TFS1990's avatar
      TFS1990
      Copper Contributor
      JKPieterse Thank you for the response not sure I can get this to work?
      Any other alternatives maybe even using a different formula than SUMIFS to achieve the same result?
      • JKPieterse's avatar
        JKPieterse
        Silver Contributor

        TFS1990 I would suggest to pull the relevant table into a separate worksheet using Data, Get Data, From File, From workbook. Then have your SUMIFS refer to that new table. You can set the new table to auto-refresh when the file opens.

Resources