Sumifs

[Memar]

I have used a SUMIFS function in workbook A to look up a value from a different workbook (workbook B) based on a three criteria. The formula works perfectly as long as the other workbook (B) is opened. When I close workbook A and reopen it with out opening workbook B, I get a "VALUE" until I open workbook B. I don't want to open workbook B all the time I work on workbook A. Is there a way to use the SUMIFS and get a desired value with out opening workbook B? I have tried INDEX and MATCH before, but the spreadsheet is very slow to open up ( take a minute to calculating 8 processors). What is the best function to look up a value in a long range with out slowing the speed of calculating, opening and processing a workbook?

SUMIFS formula used-
SUMIFS(workbookB!L2:L30000, WorkbookB!A2:A30000,A4,WorkbookB!B2:B30000,C2,Workbook!D2:D30000,D2)

Thanks for your help and time!

 

[Tetra201]

Try

=SUMPRODUCT(WorkbookB!L2:L30000,--(WorkbookB!A2:A30000=A4),--(WorkbookB!B2:B30000=C2),--(WorkbookB!D2:D30000=D2))

 

[Memar]

Thank you for reply. I get only zero values by using this formula.

SUMPRODUCT(WorkbookB!L2:L30000,--(WorkbookB!A2:A30000=A4),--(WorkbookB!B2:B30000=C2),--(WorkbookB!D2:D30000=D2))

 

[Tetra201]

The suggested formula has been tested to work.

If you post your actual SUMIFS (the formula in Post # 1 does not seem to be the actual one), some further tweaking might be done.

Happy New Year!

 

[Memar]

Here is the exact formula. Thank you very much!

=SUMIFS('MMM.xlsx]Sheet1'!$K$2:$K$60000,'[MMM.xlsx]Sheet1'!$B$2:$B$60000,$A$3,'[MMM.xlsx]Sheet1'!$F$2:$F$60000,$B6,'[MMM.xlsx]Sheet1'!$A$2:$A$60000,E$4)

Happy New Year!!!

 

[Tetra201]

Please try the following formula -- it's bee tested to work:

=SUMPRODUCT('[MMM.xlsx]Sheet1'!$K$2:$K$60000,--('[MMM.xlsx]Sheet1'!$B$2:$B$60000=$A$3),--('[MMM.xlsx]Sheet1'!$F$2:$F$60000=$B6),--('[MMM.xlsx]Sheet1'!$A$2:$A$60000=E$4))