SUMIFS to SUMPRODUCT

KevinMMO

New Member
Joined
Mar 2, 2018
Messages
17
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):
[TABLE="width: 93"]
<tbody>[TR]
[TD]=SUMIFS(G5:G50,A5:A50,"="&A25,H5:H50,"="&H25)[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[TABLE="width: 93"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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).
 
Upvote 0
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
 
Last edited:
Upvote 0
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 !?
 
Upvote 0
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>


[TABLE="class: grid, width: 901"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]With Pasta 1 Open and Pasta 2 Close[/TD]
[TD]With Pasta 1 Open and Pasta 2 Close[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]=SUMIFS('C:\Link\[Pasta 2.xlsx]Plan1'!$G$5:$G$10,[/TD]
[TD]=SUMPRODUCT('C:\Link\[Pasta 2.xlsx]Plan1'!$G$5:$G$10,[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]'C:\Link\[Pasta 2.xlsx]Plan1'!$A$5:$A$10,A25,[/TD]
[TD]--('C:\Link\[Pasta 2.xlsx]Plan1'!$A$5:$A$10=A25),[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]'C:\Link\[Pasta 2.xlsx]Plan1'!$H$5:$H$10,G25)[/TD]
[TD]--('C:\Link\[Pasta 2.xlsx]Plan1'!$H$5:$H$10=G25))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]SUMIFS[/TD]
[TD]SUMPRODUCT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD="align: center"]#VALOR![/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]01/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*********************************************[/TD]
[TD]***************************************************[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Upvote 0

Forum statistics

Threads
1,223,987
Messages
6,175,795
Members
452,670
Latest member
nogarth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top