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

#### Lukewz

##### New Member
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!

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### steve the fish

##### Well-known Member
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)

Last edited:

#### Lukewz

##### New Member
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

Replies
11
Views
906
Replies
3
Views
513
Replies
5
Views
490
Replies
14
Views
946
Replies
7
Views
2K

1,191,627
Messages
5,987,762
Members
440,109
Latest member
mitra2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back