SUMPRODUCT no results unless other file is open?

[samilynn]

I am using the following formula, but it keeps giving me #VALUE! if the other worksheet is not open. If it is open however, it give me the proper results. Any suggestions on how to make this formula work when the SALES.xlsx files is not open?

=SUMPRODUCT(SUMIFS('\\SERVER-01\home\user\WORKSHEETS\[SALES.xlsx]Sheet1'!$B$2:$B$25,'\\SERVER-01\home\user\WORKSHEETS\[SALES.xlsx]Sheet1'!$A$2:$A$25,Mgr!$A$4:$A$10))

Thank you so much,
Samantha

 

[Joe4]

The issue is not with SUMPRODUCT (which will work on closed files), but rather with your use of SUMIFS (which does not work on closed files).
Change your SUMIFS to SUMPRODUCT, and I think it should work.

 

[samilynn]

The issue is not with SUMPRODUCT (which will work on closed files), but rather with your use of SUMIFS (which does not work on closed files).
Change your SUMIFS to SUMPRODUCT, and I think it should work.

Hi Joe, thanks for the recommendation. I'm having a hard time getting it to work, I'm still getting an error message
Mind showing me how you would do it?
Thank you!

 

[Joe4]

I am a bit confused as to what you are doing with your SUMIFS formula, as the ranges you are using in them appear to be different sizes.
Can you show us a sample of the data you have on this sheet, and explain exactly what you are trying to return from it?

 

[samilynn]

I am a bit confused as to what you are doing with your SUMIFS formula, as the ranges you are using in them appear to be different sizes.
Can you show us a sample of the data you have on this sheet, and explain exactly what you are trying to return from it?

Hi Joe.
So Column A in [SALES.xlsx] has a list of salesreps. Column B has sales for each respective rep. Then this range Mgr!$A$4:$A$10 has a list of which reps are assigned to Team A. I am trying to get a total of the sales in Column B but only for the reps under Team A.

I hope I explained it clearly, I definitely confuse myself often. Thanks for your help!!!

 

[Joe4]

Try this:

=SUMPRODUCT(--ISNUMBER(MATCH('\\SERVER-01\home\user\WORKSHEETS\[SALES.xlsx]Sheet1'!$A$2:$A$25,Mgr!$A$4:$A$10,0)),'\\SERVER-01\home\user\WORKSHEETS\[SALES.xlsx]Sheet1'!$B$2:$B$25)

(hopefully I did not make any errors in the cut and pasting of your ranges)

 

[samilynn]

Try this:

=SUMPRODUCT(--ISNUMBER(MATCH('\\SERVER-01\home\user\WORKSHEETS\[SALES.xlsx]Sheet1'!$A$2:$A$25,Mgr!$A$4:$A$10,0)),'\\SERVER-01\home\user\WORKSHEETS\[SALES.xlsx]Sheet1'!$B$2:$B$25)

(hopefully I did not make any errors in the cut and pasting of your ranges)

It works perfect, thank you SO MUCH!!!

PS: I want to learn SUMPRODUCT better, can you recommend how to go about it? youtube....etc...thanks

 

[Joe4]

Quite frankly, I seldom use SUMPRODUCT anymore. There are so many new and cool functions in Excel 365, I seldom have need for it.
But it can be useful when referencing closed workbooks.

If you do a Google/YouTube search, you can find lots of great material on it.
Here is one: Excel SUMPRODUCT function with multiple criteria - formula examples