**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.*