Sumifs does not work when a workbook is closed

zeidhaddad

New Member
Joined
Oct 4, 2019
Messages
14
=SUMMEWENNS('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$C$2:$C$1703;'[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$J$2:$J$1703;B19;'[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A$2:$A$1703;F6)

as well all know whenever i close the other workbook it gives #Value, can anyone help me to translate it into a working function when the workbook is closed ?
i read something about SumProduct but i did not understand it very well so HELP!!!!!!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Re: Sumfs does not work when a workbook is closed

Try...

=SUMPRODUCT('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$C$2:$C$1703,--('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$J$2:$J$1703=B19),--('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A$2:$A$1703=F6))

You'll need to change the formula to suit your version of Excel.

Hope this helps!
 
Upvote 0
Re: Sumfs does not work when a workbook is closed

Try...

=SUMPRODUCT('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$C$2:$C$1703,--('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$J$2:$J$1703=B19),--('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A$2:$A$1703=F6))

You'll need to change the formula to suit your version of Excel.

Hope this helps!


the thing is SumIFs can’t be used for external workbook when it’s closed.I’m looking for a function or set of functions where i can calculate the total number of sales for a specific product on a specific Date.

my excel version is 2013. the one you send did not work. any more thoughts?
Thanks you so much for your effort and fast reply!
 
Upvote 0
Re: Sumfs does not work when a workbook is closed

My version of Excel uses the comma as a list separator, whereas yours uses a semi-colon. Did you replace the commas with semi-colons? Also, did you change SUMPRODUCT to your language version?
 
Upvote 0
Re: Sumfs does not work when a workbook is closed

Mr.Domenic thank you so much!! i owe you big time! :) have a great weekend
 
Upvote 0
Re: Sumfs does not work when a workbook is closed

That's great, I'm glad I could help.

You too, have a great weekend.

Cheers!
 
Upvote 0
Re: Sumfs does not work when a workbook is closed

hello! i wanted to ask you smthn.

do you have any idea how to translate CountIfs to Sumproduct so i can close the workbook?

=ZÄHLENWENNS('C:\Users\ve023g\Desktop\zeid\Zeid Workshop\[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A:$A;F6;'C:\Users\ve023g\Desktop\zeid\Zeid Workshop\[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$J:$J;B19)
 
Upvote 0
Re: Sumfs does not work when a workbook is closed

Here's the English version, which uses the comma as a list separator...

=SUMPRODUCT(--('C:\Users\ve023g\Desktop\zeid\Zeid Workshop\[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A:$A=F6),--('C:\Users\ve023g\Desktop\zeid\Zeid Workshop\[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$J:$J=B19))

By the way, I would suggest that you avoid whole column references. Otherwise, you'll find the calculations to be very slow. Also, I noticed that you have a space after the sheet name Daten. Is this correct?
 
Upvote 0
Re: Sumfs does not work when a workbook is closed

Here's the English version, which uses the comma as a list separator...

=SUMPRODUCT(--('C:\Users\ve023g\Desktop\zeid\Zeid Workshop\[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A:$A=F6),--('C:\Users\ve023g\Desktop\zeid\Zeid Workshop\[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$J:$J=B19))

By the way, I would suggest that you avoid whole column references. Otherwise, you'll find the calculations to be very slow. Also, I noticed that you have a space after the sheet name Daten. Is this correct?


Yes thats correct now i have a bigger problem with this =ZÄHLENWENNS('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A:$A;">="&'[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A$1684;'[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$J:$J;B19) since it includes date!
 
Upvote 0
Re: Sumfs does not work when a workbook is closed

Try...

=SUMPRODUCT(--('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A:$A>='[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A$1684),--('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$J:$J=B19))
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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