SUMIFS gives #VALUE when external file is closed

I have a SUMIF formula linked to an external file that only works when the file is open. I realize this is how Excel works, but I've tried SUMPRODUCT and that doesn't seem to work. I'm needing some thoughts on how I can get this formula to work.

=SUMIFS('[KAM Forecasts_sales-consensus.xlsx]Sheet1'!$I:$I,'[KAM Forecasts_sales-consensus.xlsx]Sheet1'!$C:$C,E5,'[KAM Forecasts_sales-consensus.xlsx]Sheet1'!$G:$G,K5,'[KAM Forecasts_sales-consensus.xlsx]Sheet1'!$B:$B,D5)

What I'm trying to accomplish is this: I need to pull in specific data (I:I) based on Profit Center (E5), Date (K5), and Customer (D5). In essence this is a lookup formula, but SUMIFS is the only thing I could get to work properly.

I've researched endlessly on how to correct this, but nothing seems to work.

I'm thankful for any help!

I believe you need to include the entire file path to get the info from a closed workbook...

Please Login or Register  to view this content.

Thank you for the response. The entire path shows up when the file is closed. What I'm showing there is what it looks like when the file is open. I can't send all the external files along with this file to our team, and unfortunately I can't move the external data into this file in another tab because of how the external data is pulled. I really need a formula that will lookup the exact data I need when the external file is closed.

SUMIFS doesn't work on closed workbooks.

Create the SUMPRODUCT formula with the other workbook open so that the entire file path will be included.

After the formula is created and the source workbook is closed, your formula should look something like this:

=SUMPRODUCT(
('[KAM Forecasts_sales-consensus.xlsx]Sheet1'!$I:$I)*
('[KAM Forecasts_sales-consensus.xlsx]Sheet1'!$C:$C=E5)*
('[KAM Forecasts_sales-consensus.xlsx]Sheet1'!$G:$G=K5)*
('[KAM Forecasts_sales-consensus.xlsx]Sheet1'!$B:$B=D5))

That being said, SUMPRODUCT will be very slow if you use whole column references. You should limit these as much as possible.
Something like $I$2:$I$1000 instead of $I:$I, for example.

Originally Posted by dangelor

I believe you need to include the entire file path to get the info from a closed workbook...

I believe that the formula was copied while the workbook was open. If you look at any formula that refers to another workbook, you will see that the full path is only shown when the other workbook is closed (exception being a path generated by INDIRECT).

To work with a closed workbook, SUMPRODUCT will be required

Formula:

Please Login or Register  to view this content.

63falcondude - I just typed the exact formula you have and I get "0" now instead of #VALUE!. I did change from whole column references to exact references and am still getting "0". I've looked at the data I'm referencing and it's part of a pivot table and shows "custom" instead of "number" so I'm not sure if that's causing it to come back as "0". The only problem is I cannot change the data to "number" because it's an SAP Analysis data pull and you cannot change the format because it will go right back when updated.

If it is imported data is formatted as text then changing the criteria in sumproduct to

Formula:

Please Login or Register  to view this content.

might solve it.

Jason.b75 - thank you for the formula, however just like the formula 63falcondude gave I get "0" instead of the actual value I'm looking for. I'm wondering if I should be using an Index/Match formula to lookup the data and pull it in? However, I've only used that one time with another file that was pulling data in a much different way.

I just converted from SUMIFS to SUMPRODUCT. They should do the same thing.

I can't say why it isn't working without seeing a sample in front of me.

I don't think formatting is the issue here.

Try the formula in the source workbook to see if it works in there.

Still no luck. I may just leave it and make sure my team knows not to update the links when they open the file. I've been working on this way too long and I'm just not sure what else I can do.

Do you think Index/Match would work for what I'm trying to do?

INDEX MATCH doesn't add.

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

I think I'm using the wrong formula because all I want to do is lookup a cell & grab that value, however SUMIFS was the only thing I could come up with that actually worked.

I'll upload a file shortly.

Did you try my second suggestion from post #7? I think that should work if the data in the source file is being formatted as text.

Given that your opening post implies that the sumifs formula works with the workbook open, this is the only thing that I can think of which would cause the problem.

Jason.b75 - My apologies, I missed your response #7. This worked perfectly!!! Thank you so much for your help.

You're welcome! No apolgy required, it's easy to miss a reply when you have several people posting at the same time.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.