Hi I have an excel sumif formula in attached file called linked book1. The range is located in another external excel book called linked file 2. However the cell shows an error value whenever linked file 2 book is closed. Is there a way to update the formula not to show the error value.
SUMIF continues to be a problem for closed workbooks. Switch to the old school SUMPRODUCT
=SUMPRODUCT(('C:\Users\spilla1x\Downloads\[Test report 1 (1) (1) (1) (1).xlsx]Test File 1'!$H$2:$H$9=Sheet1!A$1),'C:\Users\spilla1x\Downloads\[Test report 1 (1) (1) (1) (1).xlsx]Test File 1'!$E$2:$E$8)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi, I seem to have an error value with the sumproduct formula. Am i missing something? Cell A2 in linked book1 is to calculate the total submissions in column E based on the collection date in column H from linked Book2.
Last edited by Shamz41; 05-25-2017 at 03:18 AM.
This worked with your test files:
=SUMPRODUCT(('[Linked Book2.xlsx]Test File 1'!$H$2:$H$8=Sheet1!A$1)*('[Linked Book2.xlsx]Test File 1'!$E$2:$E$8))
I can't see why the formula posted by Jerry does not work.
Did you copy/paste the formula?
It works now. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks