Sumifs does not work when a workbook is closed

[zeidhaddad]

=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!!!!!!

 

[Domenic]

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!

 

[zeidhaddad]

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!

 

[Domenic]

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?

 

[zeidhaddad]

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

 

[Domenic]

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!

 

[zeidhaddad]

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)

 

[Domenic]

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?

 

[zeidhaddad]

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!

 

[Domenic]

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))