Convert SUMIF formula to SUMPRODUCT as SUMIF doesnt work in closed workbook reference

[mahnumtahir]

Hello,
Based on my research on this forum, I've learn't that SUMIFS formula does not work when the source data workbook is closed, once the recepient workbook is refreshed the values go to #VALUE error.
I've read that the SUMPRODUCT formula can be manipulated to achieve the same result and works when the source reference workbook gets closed.

This is my SUMIFS formula that does work when the source workbook is open:
=SUMIFS('[PQ July.XLSX]Sheet1'!$F:$F,'[PQ July.XLSX]Sheet1'!$A:$A,D26,'[PQ July.XLSX]Sheet1'!$B:$B,E26,'[PQ July.XLSX]Sheet1'!$G:$G,">="&"7/1/2019",'[PQ July.XLSX]Sheet1'!$G:$G,"<="&"7/31/2019")

How can I convert it a SUMPRODUCT formula to accommodate for the multiple criteria?

 

[RoryA]

Welcome to the forum.

Firstly, don't use entire column references when using SUMPRODUCT like this (i.e. with criteria arrays). So you could use this for example:

=SUMPRODUCT('[PQ July.XLSX]Sheet1'!$F$1:$F$1000,('[PQ July.XLSX]Sheet1'!$A$1:$A$1000=D26)*('[PQ July.XLSX]Sheet1'!$B$1:$B$1000=E26)*('[PQ July.XLSX]Sheet1'!$G$1:$G$1000>=DATE(2019,7,1))*('[PQ July.XLSX]Sheet1'!$G$1:$G$1000<=DATE(2019,7,31)))

 

[mahnumtahir]

hello! thankyou for your reply. I have tried this formula now the answer results to "-" dash sign on all the cells the formula was placed in. what could be the cause?thanks again!

 

[RoryA]

That would suggest it is returning 0.

 

[mahnumtahir]

That would suggest it is returning 0.

I understand, however the answer is non-zero

 

[RoryA]

Hard to answer without seeing the data. Are any of the criteria numbers?

 

[mahnumtahir]

Hard to answer without seeing the data. Are any of the criteria numbers?

One criteria is the date as you have helped me out with
One of the criteria contains alphabets mixed with numbers for e.b A12345
The last criteria is all numbers like 5491002 for e.g

 

[RoryA]

Then I'd suspect that you have numbers stored as text in one file and not the other. SUMIFS would automatically convert them when calculating, but SUMPRODUCT won't. Are those numbers right- or left-aligned in each file? If left-aligned, they are actually text. Depending on which file has text and which numbers, you'll either need to convert the criterion in the formula to text or number. Assuming column E is that criterion, try either of:

=SUMPRODUCT('[PQ July.XLSX]Sheet1'!$F$1:$F$1000,('[PQ July.XLSX]Sheet1'!$A$1:$A$1000=D26)*('[PQ July.XLSX]Sheet1'!$B$1:$B$1000=TEXT(E26,"0"))*('[PQ July.XLSX]Sheet1'!$G$1:$G$1000>=DATE(2019,7,1))*('[PQ July.XLSX]Sheet1'!$G$1:$G$1000<=DATE(2019,7,31)))

or:

=SUMPRODUCT('[PQ July.XLSX]Sheet1'!$F$1:$F$1000,('[PQ July.XLSX]Sheet1'!$A$1:$A$1000=D26)*('[PQ July.XLSX]Sheet1'!$B$1:$B$1000=E26+0)*('[PQ July.XLSX]Sheet1'!$G$1:$G$1000>=DATE(2019,7,1))*('[PQ July.XLSX]Sheet1'!$G$1:$G$1000<=DATE(2019,7,31)))

 

[mahnumtahir]

Then I'd suspect that you have numbers stored as text in one file and not the other. SUMIFS would automatically convert them when calculating, but SUMPRODUCT won't. Are those numbers right- or left-aligned in each file? If left-aligned, they are actually text. Depending on which file has text and which numbers, you'll either need to convert the criterion in the formula to text or number. Assuming column E is that criterion, try either of:

=SUMPRODUCT('[PQ July.XLSX]Sheet1'!$F$1:$F$1000,('[PQ July.XLSX]Sheet1'!$A$1:$A$1000=D26)*('[PQ July.XLSX]Sheet1'!$B$1:$B$1000=TEXT(E26,"0"))*('[PQ July.XLSX]Sheet1'!$G$1:$G$1000>=DATE(2019,7,1))*('[PQ July.XLSX]Sheet1'!$G$1:$G$1000<=DATE(2019,7,31)))

or:

=SUMPRODUCT('[PQ July.XLSX]Sheet1'!$F$1:$F$1000,('[PQ July.XLSX]Sheet1'!$A$1:$A$1000=D26)*('[PQ July.XLSX]Sheet1'!$B$1:$B$1000=E26+0)*('[PQ July.XLSX]Sheet1'!$G$1:$G$1000>=DATE(2019,7,1))*('[PQ July.XLSX]Sheet1'!$G$1:$G$1000<=DATE(2019,7,31)))

still no luck unfortunately

 

[RoryA]

I'd have to see the file then, I think. Can you put it on a sharing site like OneDrive or Dropbox?