SUMIFS between two different workbooks (SUMPRODUCT solution needed)

[grant1991]

Hi,

I have recently discovered that SUMIFS only works between two different workbooks if both workbooks are open at the same time.

I've also read to get round this, SUMPRODUCT can be used. That said I can't get it working.

The SUMIFS version of the formula I need converting to SUMPRODUCT is below

=SUMIFS('[Historical Performance Data.xlsx]RAW'!$I:$I,'[Historical Performance Data.xlsx]RAW'!$N:$N,C$6,'[Historical Performance Data.xlsx]RAW'!$P:$P,$C$3,'[Historical Performance Data.xlsx]RAW'!$F:$F,$B8)

Can someone help me convert this to SUMPRODUCT?

I would also need the below converting as well.

=SUMIFS('[Category Spend Report (v4).xlsx]Budgets-By Cat By Week - NEW'!$D:$D,'[Category Spend Report (v4).xlsx]Budgets-By Cat By Week - NEW'!$B:$B,G$6)

Cheers,
Grant

 

[Special-K99]

Try

=SUMPRODUCT(('[Historical Performance Data.xlsx]RAW'!$N:$N=C$6)*('[Historical Performance Data.xlsx]RAW'!$P:$P=$C$3)*('[Historical Performance Data.xlsx]RAW'!$F:$F=$B8)*(
'[Historical Performance Data.xlsx]RAW'!$I:$I))

and

=SUMPRODUCT(('[Category Spend Report (v4).xlsx]Budgets-By Cat By Week - NEW'!$B:$B=G$6)*('[Category Spend Report (v4).xlsx]Budgets-By Cat By Week - NEW'!$D:$D))

 

[grant1991]

Try

=SUMPRODUCT(('[Historical Performance Data.xlsx]RAW'!$N:$N=C$6)*('[Historical Performance Data.xlsx]RAW'!$P:$P=$C$3)*('[Historical Performance Data.xlsx]RAW'!$F:$F=$B8)*(
'[Historical Performance Data.xlsx]RAW'!$I:$I))

and

=SUMPRODUCT(('[Category Spend Report (v4).xlsx]Budgets-By Cat By Week - NEW'!$B:$B=G$6)*('[Category Spend Report (v4).xlsx]Budgets-By Cat By Week - NEW'!$D:$D))

Hey,

Thanks for your reply! When I paste in that formula it throws up a #VALUE ! error. Any ideas?

Cheers,
Grant

 

[grant1991]

Hey,

Thanks for your reply! When I paste in that formula it throws up a #VALUE ! error. Any ideas?

Cheers,
Grant

Hi,

Any further thoughts here?

Thanks,
Grant

 

[sandy666]

maybe read here: #VALUE error

 

[FormR]

that formula it throws up a #VALUE ! error. Any ideas?

Hi, welcome to the forum!

Text values in the sum range could be the issue, using SUMPRODUCT() in the below form should alleviate that problem.

=SUMPRODUCT('[Historical Performance Data.xlsx]RAW'!$I$1:$I$1000,--('[Historical Performance Data.xlsx]RAW'!$N$1:$N$1000=C$6),--('[Historical Performance Data.xlsx]RAW'!$P$1:$P$1000=$C$3),--('[Historical Performance Data.xlsx]RAW'!$F$1:$F$1000=$B8))

=SUMPRODUCT('[Category Spend Report (v4).xlsx]Budgets-By Cat By Week - NEW'!$D$1:$D$1000,--('[Category Spend Report (v4).xlsx]Budgets-By Cat By Week - NEW'!$B$1:$B$1000=G$6))

Note, for efficiency reasons you should avoid using full column references with this type of formula, instead use sensible maximums.

 

[grant1991]

Worked a treat! Thanks for the help.

Noted re using sensible maximums with column references.

Thanks again!

Grant