SUMPRODUCT & MULTIPLE SHEETS PROBLEM

minsk2000

Board Regular
Joined
Dec 25, 2005
Messages
68
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
Joined
Dec 25, 2005
Messages
68
It desn't work with SUMIF as well.

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top