# Excel - IF Function Problem - Expert Solution

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
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
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
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
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
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
User 08/04/2018 - 01:17
that file doesnt reference any other workbooks
Excelchat Expert 08/04/2018 - 01:17
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.