DOMENIC, I'm getting a #ref error with this formula.
=SUMPRODUCT((THREED(Data1:Data2!$A$4:$A$291)=Summa ry!$B$1)*(THREED(
Data1:Data2!$H$4:$H$291)=Summary!$A33),THREED(Data 1:Data2!I$4:I$291))

can you help? B.

"Domenic" wrote:

If you download and install the free add-in Morefunc.xll, you can use
the THREED function...

=SUMPRODUCT(--(THREED('Data1:Data5'!$A$4:$A$291)=Summary!$B$1),--(THREED(
'Data1:Data5'!$H$4:$H$291)=Summary!$A33),THREED('D ata1:Data5'!I$4:I$291))

The add-in can be found at the following link...

http://xcell05.free.fr/

Without the add-in, you can use the following, much less efficient,
formula...

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&A1:E1&"'!A4:A291"),ROW(INDI RECT("4:2
91"))-4,0,1))=Summary!$B$1),--(T(OFFSET(INDIRECT("'"&A1:E1&"'!H4:H291"),R
OW(INDIRECT("4:291"))-4,0,1))=Summary!$A$33),N(OFFSET(INDIRECT("'"&A1:E1 &
"'!I4:I291"),ROW(INDIRECT("4:291"))-4,0,1)))

....where A1:E1 contains your list of sheet names. Note that your list
of sheet names has to be entered in a horizontal range of cells. Also,
I've assumed that Column A and Column H contain text values. If a
column contains numerical values instead, change this part of the
formula...

--(T(OFFSET(INDIRECT

to

--(N(OFFSET(INDIRECT

....for the appropriate column or columns.

Hope this helps!

In article ,
"BSantos" wrote:

Hi, I'm still trying to solve this formula. I'm not an expert and am new to
sumproduct. I have a summary sheet that looks something like this.

A B C D
E
sku# 122-344
Jan Feb Mar
2006 (formula 1)
2005
region Count of stores Jan Feb Mar
1 (formula 2) (formula 3).......
3
5

Ranking Jan Feb Mar April
A (formula 4)
B
C

I have at least 7 Data sheets that a sku# could be on any of these sheets. I
want a formula to look at all those sheets and..
formula 1. sum the designated column if it found that sku #
formula 2. count how many stores it found that sku #
formula 3. sum the sku # if it also found the region
formula 4. sum the sku # if it also found the ranking

I'm sorry I don't know how to nest multiple sheets into a sumproduct.

Can someone help me please! Bonnie

Example: but doesn't work
=SUMPRODUCT((Data1!$A$4:$A$291=Summary!$B$1)*(Data 1!$H$4:$H$291=Summary!$A33)*
(Data1!I$4:I$291))and(Data2!$A$4:$A$291=Summary!$B $1)*(Data2!$H$4:$H$291=Summa
ry!$A33)*(Data2!I$4:I$291))and(Data3!$A$4:$A$291=S ummary!$B$1)*(Data3!$H$4:$H$
291=Summary!$A33)*(Data3!I$4:I$291))and(Data4!$A$4 :$A$291=Summary!$B$1)*(Data4
!$H$4:$H$291=Summary!$A33)*(Data4!I$4:I$291))and(D ata5!$A$4:$A$291=Summary!$B$
1)*(Data5!$H$4:$H$291=Summary!$A33)*(Data5!I$4:I$2 91))