SUMIFS Closed Workbook Workaround with Multiple Criteria Array
Results 1 to 2 of 2

Thread: SUMIFS Closed Workbook Workaround with Multiple Criteria Array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIFS Closed Workbook Workaround with Multiple Criteria Array

    Hello,

    I am having trouble figuring out a way to make this formula work when the source workbook is closed:

    =SUM(SUMIFS('Workbook A.xlsx]Sheet1'!$E$2:$E$2000,'[Workbook A.xlsx]Sheet1'!$B$2:$B$2000,$A2,'[Workbook A.xlsx]Sheet1'!$D$2:$D$2000,{"Apples","Oranges"}))

    This formula is working fine when Workbook A is open, and other fields in the form using a VLOOKUP referencing workbook A work when it's closed, so I believe this is an issue with the SUMIFS function.

    Microsoft says this is a known issue here: https://support.office.com/en-us/art...9-160ed5f13b5a

    I found a previous forum post addressing a similar circumstance here: https://www.mrexcel.com/forum/excel-...-workbook.html

    I couldn't figure out how to use the workarounds suggested there because I have a nested SUM and SUMIFS formula and I have an array in the criteria at the end.

    Any suggestions?

    Thank you in advance!

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,831
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: SUMIFS Closed Workbook Workaround with Multiple Criteria Array

    Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

    Code:
    =SUM(IF('[Workbook A.xlsx]Sheet1'!$B$2:$B$2000=$A2,IF(ISNUMBER(MATCH('[Workbook A.xlsx]Sheet1'!$D$2:$D$2000,{"Apples","Oranges"},0)),'[Workbook A.xlsx]Sheet1'!$E$2:$E$2000)))
    Hope this helps!
    Last edited by Domenic; Jun 24th, 2019 at 11:06 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •