# SUMPRODUCT & MULTIPLE SHEETS PROBLEM

#### minsk2000

##### Board Regular
Hi Everyone,

Just recently I have learned how to use SUMPRODUCT function instead of SUMIF one and I find it extremely useful and versatile.
If I need to find out sum of amounts between say 08/25/06 and 09/25/06 I use this formula:

=SUMPRODUCT(--(‘Sheet1’!A17:AA17>08/25/06+0),--(‘Sheet1’!A17:AA17<=09/25/06+0),’Sheet1’!A50:AA50)

… this works great if I have to lookup only one sheet.

The problem arises when I try to use the same formula for multiple sheets.

If put

=SUMPRODUCT(--(‘Sheet1:Sheet10’!A17:AA17>08/25/06+0),--(‘Sheet1:Sheet10’!A17:AA17<=09/25/06+0),’ Sheet1:Sheet10’!A50:AA50)

it shows #REF!

I tried various ways to input multiple sheets into this formula and this didn’t work.

Where am I wrong? May be this function setup doesn’t work at all with multiple sheets.
Is there any other way to accomplish the same task on multiple sheets?

Thanks for the help

Minsk

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### minsk2000

##### Board Regular
It desn't work with SUMIF as well.

#### pgc01

##### MrExcel MVP
Hi minsk2000

In fact, SUMPRODUCT, as many other functions, doesn't support 3d references.

The only way I know how to do what you want is to generate all the possible addresses you need in all the worksheets using pairs of orthogonal arrays, your horizontal vectors where you have the data and a vertical vector with the names of the worksheets.

The vector with the names of the worksheets can be defined in the worksheet or with a name. I chose the first option. I wrote the name of 3 worksheets in G1:G3. It's good for testing. If it works you can write the other worksheet names in G1:G10.

So please write in G1:G3 Sheet1, Sheet2 and Sheet3 and try

Code:
``=SUMPRODUCT(--(N(OFFSET(INDIRECT(\$G\$1:\$G\$3&"!\$A\$17"),0,COLUMN(\$A\$17:\$AA\$17)-COLUMN(\$A\$17)))>"2006-08-25"+0),--(N(OFFSET(INDIRECT(\$G\$1:\$G\$3&"!\$A\$17"),0,COLUMN(\$A\$17:\$AA\$17)-COLUMN(\$A\$17)))<="2006-09-25"+0),N(OFFSET(INDIRECT(\$G\$1:\$G\$3&"!\$A\$50"),0,COLUMN(\$A\$50:\$AA\$50)-COLUMN(\$A\$50))))``

Remarks:
1 - you had an error in the formula:
>08/25/06+0
means bigger than 8 divided by 25 divided by 6. You meant
>"08/25/06"+0

2 - I used my date format 2006-09-25, you may have to change it to your date format.

As usual, there may be simpler solutions.

Hope this helps
PGC

#### minsk2000

##### Board Regular
I'm sure there are people who know how to solve this

Thanks PGC!!!

Replies
19
Views
544
Replies
0
Views
111
Replies
6
Views
345
Replies
4
Views
105
Replies
11
Views
292

1,141,755
Messages
5,708,320
Members
421,565
Latest member

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