SUMPRODUCT & MULTIPLE SHEETS PROBLEM

[minsk2000]

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

 

[minsk2000]

It desn't work with SUMIF as well.

Guys please help me with at least a hint how to solve this.

 

[pgc01]

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

=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]

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

 

[minsk2000]

Thanks PGC!!!