Using SUMIFS to get data from another closed worksheet results in #VALUE! error

[Lukewz]

Hi there,

I'm using a sumifs formula to extract data from another worksheet. When the other worksheet is open, the values show up fine. However, when it's closed I get the #VALUE! error.

What is a work around for this? I've seen people mention sumproduct but I have no idea how that works to replace a SUMIFS function.

I'm using excel 2010.

=SUMIFS('[Sales Forecast 2014.xlsm]Datatable'!$U$11:$U$162010,'[Sales Forecast 2014.xlsm]Datatable'!$M$11:$M$162010,E14,'[Sales Forecast 2014.xlsm]Datatable'!$F$11:$F$162010,G14)

That is the current formula I'm using


Thanks!

 

[steve the fish]

Try this:

=SUMPRODUCT(--('[Sales Forecast 2014.xlsm]Datatable'!$M$11:$M$162010=E14),--('[Sales Forecast 2014.xlsm]Datatable'!$F$11:$F$162010=G14),'[Sales Forecast 2014.xlsm]Datatable'!$U$11:$U$162010)

 

[Lukewz]

delete this post please

 

[Lukewz]

Try this:

=SUMPRODUCT(--('[Sales Forecast 2014.xlsm]Datatable'!$M$11:$M$162010=E14),--('[Sales Forecast 2014.xlsm]Datatable'!$F$11:$F$162010=G14),'[Sales Forecast 2014.xlsm]Datatable'!$U$11:$U$162010)

Hi unfortunately this results in 0 values, even when a different value should of been shown