| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct looking at multiple sheets
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 =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)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct looking at multiple sheets
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)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct looking at multiple sheets
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! "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)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct looking at multiple sheets
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)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct looking at multiple sheets
I think I missed a set of brackets. Try the following instead...
=SUMPRODUCT((THREED(Data1:Data2!$A$4:$A$291)=Summa ry!$B$1)*(THREED(Data1: Data2!$H$4:$H$291)=Summary!$A33),(THREED(Data1:Dat a2!I$4:I$291))) Does this help? In article , "BSantos" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMif or SUMproduct across multiple worksheets? | Excel Worksheet Functions | |||
| How am i able to populate a sumation for multiple sheets | Excel Worksheet Functions | |||
| Matching Multiple Sheets | Excel Worksheet Functions | |||
| Conditional Sum and multiple conditions across different sheets | Excel Worksheet Functions | |||
| linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
Linear Mode
