Question description:
This user has given permission to use the problem statement for this
blog.
i heard that sumproduct can reference a closed workbook? if so how can i change my sumifs to sumproduct?
Solved by A. J. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/04/2018 - 12:40
Hi
User
08/04/2018 - 12:40
hi
Excelchat Expert
08/04/2018 - 12:40
Welcome to Got it Pro
User
08/04/2018 - 12:40
how are you
User
08/04/2018 - 12:40
thanks
Excelchat Expert
08/04/2018 - 12:40
I am fine. Thanks
Excelchat Expert
08/04/2018 - 12:40
hope you are doing good
User
08/04/2018 - 12:41
im ok r u m or f?
Excelchat Expert
08/04/2018 - 12:41
M
Excelchat Expert
08/04/2018 - 12:41
why?
User
08/04/2018 - 12:41
just wondering what you look like
Excelchat Expert
08/04/2018 - 12:41
Ok good.
User
08/04/2018 - 12:41
so anyway, can sumproduct reference a closed workbook?
Excelchat Expert
08/04/2018 - 12:42
Yes. It will work
User
08/04/2018 - 12:43
because my formula is a sumifs and that doesnot reference the closed workbook
Excelchat Expert
08/04/2018 - 12:43
I will send you two workbook and request you to check.
Excelchat Expert
08/04/2018 - 12:43
No . It will refer.
User
08/04/2018 - 12:43
no its ok you dont need to send
Excelchat Expert
08/04/2018 - 12:43
Can you please share your workbook.
User
08/04/2018 - 12:43
can i show you my sumifs and you tell me how to make it sumproduct?
Excelchat Expert
08/04/2018 - 12:43
yes . Please
Excelchat Expert
08/04/2018 - 12:44
Sumifs used for adding the values.
Excelchat Expert
08/04/2018 - 12:44
Sumproduct : First it will multiply and then add the results.
User
08/04/2018 - 12:44
ok its hard for me to send the file but i can write the formula
Excelchat Expert
08/04/2018 - 12:44
yes. Please tell me. Let me try to understand.
User
08/04/2018 - 12:44
i think the syntax is difference for sumproduct
Excelchat Expert
08/04/2018 - 12:45
yes. Are you able to see the excel sheet here
User
08/04/2018 - 12:45
i have sumifs that add amounts of time in a column depending on 2 different criteria
User
08/04/2018 - 12:45
one criteria is a simple number between 1 - 5the other criteria is between dates
User
08/04/2018 - 12:46
the formula works perfectly for me as a sumifs
Excelchat Expert
08/04/2018 - 12:46
ok.
Excelchat Expert
08/04/2018 - 12:46
First let me explain sumproduct.
User
08/04/2018 - 12:46
but when i close the 3 workbooks that the sumifs references i get the error values
User
08/04/2018 - 12:46
ok
Excelchat Expert
08/04/2018 - 12:47
Just look at this file.
User
08/04/2018 - 12:47
ok
Excelchat Expert
08/04/2018 - 12:47
=sumproduct(B2:B7,C2:C7)
Excelchat Expert
08/04/2018 - 12:47
=sumproduct(QTY,Price)
User
08/04/2018 - 12:48
let me show you my sumifs so you can get an idea
Excelchat Expert
08/04/2018 - 12:48
yes please.
User
08/04/2018 - 12:48
=SUMIFS('[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$V:$V,'[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$X:$X,"1",'[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$W:$W,">="&Sheet1!$C$3,'[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$W:$W,"<="&Sheet1!$C$4)+
User
08/04/2018 - 12:49
this is part of the formula i use
User
08/04/2018 - 12:49
you will notice the "+" at the end
User
08/04/2018 - 12:49
that is because i add about another 20 of these sumifs
Excelchat Expert
08/04/2018 - 12:49
Ok.
Excelchat Expert
08/04/2018 - 12:50
Request you to share the file.
User
08/04/2018 - 12:50
just so you know, the formula works well and i dont need to use sumproduct, i can use anythiong that will reference the closed workbook i dont care how
User
08/04/2018 - 12:51
sorry i can not share the book asits confidential for work
Excelchat Expert
08/04/2018 - 12:51
=SUMIFS('C:\Users\daniel.i\Desktop\[Book13.xlsx]Sheet1'!$K:$K,'C:\Users\daniel.i\Desktop\[Book13.xlsx]Sheet1'!$J:$J,C1)
User
08/04/2018 - 12:51
so i think all i need to do is use something else than sumifs
Excelchat Expert
08/04/2018 - 12:52
See My sumIfs working perfectly in the closed workbook.
User
08/04/2018 - 12:52
i have been led to believe that sumifs dont work when the book they reference is closed
Excelchat Expert
08/04/2018 - 12:52
No.. Any of the formula in excel its not like that.
User
08/04/2018 - 12:53
and i used "shift/control/enter" to make it an array and that also did not work
Excelchat Expert
08/04/2018 - 12:53
">="&Sheet1!$C$3
Excelchat Expert
08/04/2018 - 12:53
is it Date?
User
08/04/2018 - 12:54
yes
User
08/04/2018 - 12:54
$C$3 and $C$4 are each end of a date range
Excelchat Expert
08/04/2018 - 12:54
ok. Let me check
Excelchat Expert
08/04/2018 - 12:56
What is Sheet1 here?
User
08/04/2018 - 12:57
sheet 1is in the workbok im using and it is the date range refs
User
08/04/2018 - 12:57
the problem is when i close the other workbooks that im referencing info from
Excelchat Expert
08/04/2018 - 12:57
ok.. your closed work book is Data ops Progress TVP18101-p1810
User
08/04/2018 - 12:58
yes there are 3 different workbooks i reference all of which i want to leave closed
Excelchat Expert
08/04/2018 - 12:58
ok.. Give me 2 minutes.
Excelchat Expert
08/04/2018 - 01:02
Yes. Once we applied the date its not working
Excelchat Expert
08/04/2018 - 01:03
We are getting #Value Error.
User
08/04/2018 - 01:03
yes because the workbooks are closed
Excelchat Expert
08/04/2018 - 01:03
There are few solutions for this problem.
User
08/04/2018 - 01:03
i knew that
Excelchat Expert
08/04/2018 - 01:04
1. Consolidate all the different workbook into one workbook and apply the formula.
User
08/04/2018 - 01:04
nope i csant do that
User
08/04/2018 - 01:04
they are the way they are for a reason
Excelchat Expert
08/04/2018 - 01:05
oK.
User
08/04/2018 - 01:05
if i could change them i would just move the data into the sheet im using
User
08/04/2018 - 01:05
so can i use sumproduct or not?
User
08/04/2018 - 01:05
or will that also fail with time range
Excelchat Expert
08/04/2018 - 01:05
sumproduct will not work.
Excelchat Expert
08/04/2018 - 01:07
anyway let me check .
Excelchat Expert
08/04/2018 - 01:11
working on that function
Excelchat Expert
08/04/2018 - 01:13
Are you there
User
08/04/2018 - 01:13
yes
Excelchat Expert
08/04/2018 - 01:13
Request you to refer this file
[Uploaded an Excel file]
User
08/04/2018 - 01:14
ok ill look one moment
Excelchat Expert
08/04/2018 - 01:14
sure.
Excelchat Expert
08/04/2018 - 01:16
its working perfectly for closed workbook. I will send your another one
Excelchat Expert
08/04/2018 - 01:17
formula result
[Uploaded an Excel file]
User
08/04/2018 - 01:17
that file doesnt reference any other workbooks
Excelchat Expert
08/04/2018 - 01:17
[Uploaded an Excel file]
Excelchat Expert
08/04/2018 - 01:17
Please refer last two workbooks and included date as well for the calculation
Excelchat Expert
08/04/2018 - 01:20
did you check the formula
Excelchat Expert
08/04/2018 - 01:20
In G13
Excelchat Expert
08/04/2018 - 01:21
=SUMPRODUCT(('C:\Users\daniel.i\Desktop\[Sumproduct with condition.xlsx]Sumproduct With condition'!$B$11:$B$19="john")*('C:\Users\daniel.i\Desktop\[Sumproduct with condition.xlsx]Sumproduct With condition'!$C$11:$C$19="north")*('C:\Users\daniel.i\Desktop\[Sumproduct with condition.xlsx]Sumproduct With condition'!$F$11:$F$19>=M2)*'C:\Users\daniel.i\Desktop\[Sumproduct with condition.xlsx]Sumproduct With condition'!$D$11:$D$19)
User
08/04/2018 - 01:21
i dont understand what these fikles aare doing?
User
08/04/2018 - 01:21
hey referencing any other seperate workbooks?
Excelchat Expert
08/04/2018 - 01:21
Ok let me tell you with your example.
Excelchat Expert
08/04/2018 - 01:21
yes..
Excelchat Expert
08/04/2018 - 01:22
Just download recent two workbooks.
Excelchat Expert
08/04/2018 - 01:22
Book 13 I have applied the formula to get the result and cell reference from another workbook.
Excelchat Expert
08/04/2018 - 01:23
Example :SUMPRODUCT((B11:B19="john")*(C11:C19="north")*(E11:E19=1)*D11:D19)
User
08/04/2018 - 01:23
i looked and i got it
Excelchat Expert
08/04/2018 - 01:23
super.
Excelchat Expert
08/04/2018 - 01:23
:)
Excelchat Expert
08/04/2018 - 01:23
It will work for your function as well.
User
08/04/2018 - 01:24
t as i asked at the start, can you just make my original sumifs formula into a sumproduct?
Excelchat Expert
08/04/2018 - 01:24
Ok let me try but it will be very difficult without looking the data.
Excelchat Expert
08/04/2018 - 01:24
two minutes.
User
08/04/2018 - 01:24
i just deen the syntax
User
08/04/2018 - 01:25
i just need to know where to put the commas and other things
Excelchat Expert
08/04/2018 - 01:25
ok.
User
08/04/2018 - 01:27
can you tell me why there is a "+" after the "="?
Excelchat Expert
08/04/2018 - 01:27
=sumproduct ( ([Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$X:$X=1)* ([Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$W:$W,">="&Sheet1!$C$3 )* ([Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$W:$W,"<="&Sheet1!$C$4) * ([Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$V:$V))
Excelchat Expert
08/04/2018 - 01:28
No. that just typed ..you can remove that.
User
08/04/2018 - 01:28
ok
User
08/04/2018 - 01:28
so ill try this just a moment
Excelchat Expert
08/04/2018 - 01:28
ok.
Excelchat Expert
08/04/2018 - 01:32
?
User
08/04/2018 - 01:33
ok
Excelchat Expert
08/04/2018 - 01:33
Is it working?
Excelchat Expert
08/04/2018 - 01:33
Good :)
User
08/04/2018 - 01:33
i have one question about that sumproduct
Excelchat Expert
08/04/2018 - 01:33
yes please
User
08/04/2018 - 01:33
can i use the plus sign and add another sumproduct in a row?
User
08/04/2018 - 01:33
see this formula
Excelchat Expert
08/04/2018 - 01:33
yes..
User
08/04/2018 - 01:33
=SUMIFS('Z:\PA Distribution\Logs Machined\Dist P1801-P1810\[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$V:$V,'Z:\PA Distribution\Logs Machined\Dist P1801-P1810\[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$X:$X,"1",'Z:\PA Distribution\Logs Machined\Dist P1801-P1810\[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$W:$W,">="&$C$9,'Z:\PA Distribution\Logs Machined\Dist P1801-P1810\[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$W:$W,"<="&$C$10)+SUMIFS('Z:\PA Distribution\Logs Machined\Dist P1801-P1810\[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Interstitials'!$W:$W,'Z:\PA Distribution\Logs Machined\Dist P1801-P1810\[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Interstitials'!$Y:$Y,"1",'Z:\PA Distribution\Logs Machined\Dist P1801-P1810\[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Interstitials'!$X:$X,">="&$C$9,'Z:\PA Distribution\Logs Machined\Dist P1801-P1810\[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Interstitials'!$X:$X,"<="&$C$10)
User
08/04/2018 - 01:34
this is a small part of my original sumifs
Excelchat Expert
08/04/2018 - 01:34
hmmm.
User
08/04/2018 - 01:34
so i just use plus sign and add another sumifs
User
08/04/2018 - 01:34
can i do that with sumproduct and if so what is the syntax
User
08/04/2018 - 01:35
do i just use plus sign and then add same as the first sumproduct without the equals
Excelchat Expert
08/04/2018 - 01:35
yes..
Excelchat Expert
08/04/2018 - 01:35
Just checking one minute
Excelchat Expert
08/04/2018 - 01:36
=SUMPRODUCT((B11:B19="john")*(C11:C19="north")*(E11:E19=1)*D11:D19)+SUMPRODUCT((B11:B19="Paul")*(C11:C19="South")*(E11:E19=2)*D11:D19)
Excelchat Expert
08/04/2018 - 01:36
Yes. It will work perfectly.
Excelchat Expert
08/04/2018 - 01:36
This is our last session and request you to provide your valuable feedback and remarks.
User
08/04/2018 - 01:36
yes so just use the same without the equals each time just use plus
Excelchat Expert
08/04/2018 - 01:36
yes
Excelchat Expert
08/04/2018 - 01:36
Thanks for contacting us.
User
08/04/2018 - 01:37
of coarse i will leave the window open and quickly put the formula to use
User
08/04/2018 - 01:37
i think i can change my sumifs by changing the word sumifs to sumproduct right
Excelchat Expert
08/04/2018 - 01:37
No No. you have to completely change it to sumproduct
User
08/04/2018 - 01:38
but theres not much difference in the format right
Excelchat Expert
08/04/2018 - 01:38
sumproduct( (criteria1=1)*(criteria 2 >=date) *(criteria 2)<=date)*(Numbers to multiply))
Excelchat Expert
08/04/2018 - 01:39
this is simple syntax for your scenario
User
08/04/2018 - 01:40
ok thanks a lot so just use like this sumproduct( (criteria1=1)*(criteria 2 >=date) *(criteria 2)<=date)*(Numbers to multiply))+sumproduct( (criteria1=1)*(criteria 2 >=date) *(criteria 2)<=date)*(Numbers to multiply))
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
