# Sumproduct help

#### MikeGazzy

##### New Member
I have a report that references an external workbook and utilizes sumifs statements so some of my users are getting #VALUE errors when the source workbooks aren't open. I would like to utilize sumproduct instead since I have read that it will fix this issue but I don't have any experience with it and am running into some issues trying to convert my formula. Here is the original sumifs statement I would like to convert:
=SUM(SUMIFS([Source]Sheet!'!\$I\$1:\$I\$65536,[Source]Sheet!'!\$C\$1:\$C\$65536,101,[Source]Sheet!'!\$J\$1:\$J\$65536,{"Breakage","Cycle Count","Manager Cycle Count"}, [Source]Sheet!'! \$L\$1:\$L\$65536,\$A3))
I think my main issue is converting the array formula section of my original statement but it could be something else I am missing? Thanks in advance for any help.

Thanks,

Mike

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### CA_Punit

##### Well-known Member
May be like this
SUMPRODUCT([Source]Sheet!'!\$I\$1:\$I\$65536,[Source]Sheet!'!\$C\$1:\$C\$65536=101,[Source]Sheet!'!\$J\$1:\$J\$65536={"Breakage","Cycle Count","Manager Cycle Count"},[Source]Sheet!'! \$L\$1:\$L\$65536=\$A3)

#### MikeGazzy

##### New Member
Nope, that is basically more or less what I have been trying, I also tried expanding the array formula out into 3 different statements with a + in between them but that didn't work.

#### CA_Punit

##### Well-known Member
please post a sample data using xl2bb.

#### CA_Punit

##### Well-known Member

Sorry I missed

[Source]Sheet!'!\$J\$1:\$J\$65536={"Breakage","Cycle Count","Manager Cycle Count"} will give an error,

ISNUMBER(MATCH(C3:C51,{"chicken";"Egg"},0)) and try

#### CA_Punit

##### Well-known Member
SUMPRODUCT([Source]Sheet!'!\$I\$1:\$I\$65536,--([Source]Sheet!'!\$C\$1:\$C\$65536=101),--(ISNUMBER(MATCH([Source]Sheet!'!\$J\$1:\$J\$65536,"}, {"Breakage";"Cycle Count";"Manager Cycle Count"} 0)) ,--([Source]Sheet!'! \$L\$1:\$L\$65536=\$A3))

#### CA_Punit

##### Well-known Member

Sorry I missed

[Source]Sheet!'!\$J\$1:\$J\$65536={"Breakage","Cycle Count","Manager Cycle Count"} will give an error,

ISNUMBER(MATCH(C3:C51,{"chicken";"Egg"},0)) and try
Ignore This

#### Dave Patton

##### Well-known Member
I prepared a small example and the following works.
I show two views of the formula; one with the file open and one with the file closed.
K1:K3 has the 3 criteria like Breakage

=SUMPRODUCT(--([Source.xlsm]Sheet!\$I\$1:\$I\$20),--([Source.xlsm]Sheet!\$C\$1:\$C\$20=101),--(ISNUMBER(MATCH([Source.xlsm]Sheet!\$J\$1:\$J\$20,K1:K3,0))),--([Source.xlsm]Sheet!\$L\$1:\$L\$20=\$A3))

with file closed
=SUMPRODUCT(--('D:\Excel14\[Source.xlsm]Sheet'!\$I\$1:\$I\$20),--('D:\Excel14\[Source.xlsm]Sheet'!\$C\$1:\$C\$20=101),--(ISNUMBER(MATCH('D:\Excel14\[Source.xlsm]Sheet'!\$J\$1:\$J\$20,K1:K3,0))),--('D:\Excel14\[Source.xlsm]Sheet'!\$L\$1:\$L\$20=\$A3))

#### MikeGazzy

##### New Member
I prepared a small example and the following works.
I show two views of the formula; one with the file open and one with the file closed.
K1:K3 has the 3 criteria like Breakage

=SUMPRODUCT(--([Source.xlsm]Sheet!\$I\$1:\$I\$20),--([Source.xlsm]Sheet!\$C\$1:\$C\$20=101),--(ISNUMBER(MATCH([Source.xlsm]Sheet!\$J\$1:\$J\$20,K1:K3,0))),--([Source.xlsm]Sheet!\$L\$1:\$L\$20=\$A3))

with file closed
=SUMPRODUCT(--('D:\Excel14\[Source.xlsm]Sheet'!\$I\$1:\$I\$20),--('D:\Excel14\[Source.xlsm]Sheet'!\$C\$1:\$C\$20=101),--(ISNUMBER(MATCH('D:\Excel14\[Source.xlsm]Sheet'!\$J\$1:\$J\$20,K1:K3,0))),--('D:\Excel14\[Source.xlsm]Sheet'!\$L\$1:\$L\$20=\$A3))
Perfect, that got it to work. Thanks so much.

#### Dave Patton

##### Well-known Member
Thanks for the feedback.
Dave

Replies
4
Views
281
Replies
3
Views
434
Replies
6
Views
81
Replies
1
Views
101
Replies
2
Views
81