SUMIFS to SUMPRODUCT

[KevinMMO]

Hi, I'm having a problem using the SUMIFS function with ranges from closed workbooks. I have read in internet that the #REF ! problem is caused because this function can't work while the referenced workbook is closed and found that it can be done using the SUMPRODUCT function or with SUM and IF together. What I don't know is how to put this formula into either of the options. The issue is more with the "="& syntax into another function. Here's the function that I made(the ranges used are from the workbook referenced):

=SUMIFS(G5:G50,A5:A50,"="&A25,H5:H50,"="&H25)

<tbody>
</tbody><colgroup><col></colgroup>

<tbody>
</tbody><colgroup><col></colgroup>

 

[arthurbr]

Do you mean = SUMPRODUCT(G5:G50*(A5:A50=A25)*(H5:H50=H25))
Be aware that this function works on closed wbks, but once you opened and closed it, it does not work any more

 

[KevinMMO]

I have tried the formula, but #VALUE ! appears. Maybe it's because the first range is a date? What I'm trying to do is to sum the amounts of product depending on their date (first range) and their area (second range).

 

[markmzz]

Maybe the formula below (in Pasta 1.xlsx) can helps:

=SUMPRODUCT('[Pasta 2.xlsx]Plan1'!$G$5:$G$10,
--('[Pasta 2.xlsx]Plan1'!$A$5:$A$10=A25),
--('[Pasta 2.xlsx]Plan1'!$H$5:$H$10=G25))

Markmzz

 

[KevinMMO]

The formula works, but the same error occurs when I close the workbook being referenced. Is there a formula which can be used like this and doesn't give me #REF !?

 

[markmzz]

The formula works, but the same error occurs when I close the workbook being referenced. Is there a formula which can be used like this and doesn't give me #REF !?

Here all is ok. Look at this:

=SUMPRODUCT('C:\Link\[Pasta 2.xlsx]Plan1'!G$5:$G$10,

--('C:\Link\[Pasta 2.xlsx]Plan1'!$A$5:$A$10=A25),

--('C:\Link\[Pasta 2.xlsx]Plan1'!$H$5:$H$10=G25))​

​

​

<strike></strike><strike></strike>

	A	B	C	D	E	F	G	H
1	With Pasta 1 Open and Pasta 2 Close	With Pasta 1 Open and Pasta 2 Close
17
18	=SUMIFS('C:\Link\[Pasta 2.xlsx]Plan1'!$G$5:$G$10,	=SUMPRODUCT('C:\Link\[Pasta 2.xlsx]Plan1'!$G$5:$G$10,
19	'C:\Link\[Pasta 2.xlsx]Plan1'!$A$5:$A$10,A25,	--('C:\Link\[Pasta 2.xlsx]Plan1'!$A$5:$A$10=A25),
20	'C:\Link\[Pasta 2.xlsx]Plan1'!$H$5:$H$10,G25)	--('C:\Link\[Pasta 2.xlsx]Plan1'!$H$5:$H$10=G25))
21
22	SUMIFS	SUMPRODUCT
23	#VALOR!	30
24
25	01/01/2018						A
26
***	*********************************************	***************************************************	***	***	***	***	***	***

<tbody>
</tbody>

Markmzz