sumproduct looking at multiple sheets

[Guest]

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 wor
=SUMPRODUCT((Data1!$A$4:$A$291=Summary!$B$1)*(Data1!$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=Summary!$A33)*(Data2!I$4:I$291))and(Data3!$A$4:$A$291=Summary!$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(Data5!$A$4:$A$291=Summary!$B$1)*(Data5!$H$4:$H$291=Summary!$A33)*(Data5!I$4:I$291))

 

[Domenic]

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

=SUMPRODUCT(--(THREED('Data1ata5'!$A$4:$A$291)=Summary!$B$1),--(THREED(
'Data1ata5'!$H$4:$H$291)=Summary!$A33),THREED('Data1ata5'!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(INDIRECT("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!

 

[Guest]

Domenic, Thank you that is so awesome. I'm reading what Threed is and I
believe that is exactly what I need!

Thanks so much!

 

[Guest]

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

can you help? B.

 

[Domenic]

I think I missed a set of brackets. Try the following instead...

=SUMPRODUCT((THREED(Data1ata2!$A$4:$A$291)=Summary!$B$1)*(THREED(Data1:
Data2!$H$4:$H$291)=Summary!$A33),(THREED(Data1ata2!I$4:I$291)))

Does this help?