Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT FOR MULTIPLE SHEETS
How can I use a formula like:
=SUMPRODUCT(--(Jan!$H:$H=$A3)*--(Jan!$J:$J=B$1)*--(Jan!$J:$J<=B$2),Jan!$J:$J) for multiple sheets? Following formula is not working: =+SUMPRODUCT(--(Jan:Dec!$H:$H=$A3)*--(Jan:Dec!$J:$J=B$1)*--(Jan:Dec!$J:$J<=B$2),Jan:Dec!$J:$J) It is resulting into a #Name? error. Any suggestions? Thanx in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT FOR MULTIPLE SHEETS
FARAZ QURESHI;348957 Wrote: How can I use a formula like: =SUMPRODUCT(--(Jan!$H:$H=$A3)*--(Jan!$J:$J=B$1)*--(Jan!$J:$J<=B$2),Jan!$J:$J) for multiple sheets? Following formula is not working: =+SUMPRODUCT(--(Jan:Dec!$H:$H=$A3)*--(Jan:Dec!$J:$J=B$1)*--(Jan:Dec!$J:$J<=B$2),Jan:Dec!$J:$J) It is resulting into a #Name? error. Any suggestions? Thanx in advance. Hi, in XL versions before 2007 entire columns like H:H are not allowed. If needed try H1:H65535 or better still, use dynamic references -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97606 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT FOR MULTIPLE SHEETS
.... and, of course, there are various unnecessary characters in the formula.
=+SUMPRODUCT(...) is the same as =SUMPRODUCT(...) and ...*--(...) is the same as ...*(...) -- David Biddulph "Pecoflyer" wrote in message ... FARAZ QURESHI;348957 Wrote: How can I use a formula like: =SUMPRODUCT(--(Jan!$H:$H=$A3)*--(Jan!$J:$J=B$1)*--(Jan!$J:$J<=B$2),Jan!$J:$J) for multiple sheets? Following formula is not working: =+SUMPRODUCT(--(Jan:Dec!$H:$H=$A3)*--(Jan:Dec!$J:$J=B$1)*--(Jan:Dec!$J:$J<=B$2),Jan:Dec!$J:$J) It is resulting into a #Name? error. Any suggestions? Thanx in advance. Hi, in XL versions before 2007 entire columns like H:H are not allowed. If needed try H1:H65535 or better still, use dynamic references -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97606 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT FOR MULTIPLE SHEETS
XL 2007 is the version being used & the removal of unnecessary characters
also doesn't help, i.e.: =SUMPRODUCT((Jan:Dec!$H1:$H10000=$A289)*(Jan:Dec!$ J1:$J10000=B$1)*(Jan:Dec!$J1:$J10000<=B$2),Jan:De c!$J1:$J10000) is still not working. Any idea? "David Biddulph" wrote: .... and, of course, there are various unnecessary characters in the formula. =+SUMPRODUCT(...) is the same as =SUMPRODUCT(...) and ...*--(...) is the same as ...*(...) -- David Biddulph "Pecoflyer" wrote in message ... FARAZ QURESHI;348957 Wrote: How can I use a formula like: =SUMPRODUCT(--(Jan!$H:$H=$A3)*--(Jan!$J:$J=B$1)*--(Jan!$J:$J<=B$2),Jan!$J:$J) for multiple sheets? Following formula is not working: =+SUMPRODUCT(--(Jan:Dec!$H:$H=$A3)*--(Jan:Dec!$J:$J=B$1)*--(Jan:Dec!$J:$J<=B$2),Jan:Dec!$J:$J) It is resulting into a #Name? error. Any suggestions? Thanx in advance. Hi, in XL versions before 2007 entire columns like H:H are not allowed. If needed try H1:H65535 or better still, use dynamic references -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97606 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT FOR MULTIPLE SHEETS
FARAZ QURESHI;349040 Wrote: XL 2007 is the version being used & the removal of unnecessary characters also doesn't help, i.e.: =SUMPRODUCT((Jan:Dec!$H1:$H10000=$A289)*(Jan:Dec!$ J1:$J10000=B$1)*(Jan:Dec!$J1:$J10000<=B$2),Jan:De c!$J1:$J10000) is still not working. Any idea? "David Biddulph" wrote: .... and, of course, there are various unnecessary characters in the formula. =+SUMPRODUCT(...) is the same as =SUMPRODUCT(...) and ...*--(...) is the same as ...*(...) -- David Biddulph "Pecoflyer" wrote in message ... FARAZ QURESHI;348957 Wrote: How can I use a formula like: =SUMPRODUCT(--(Jan!$H:$H=$A3)*--(Jan!$J:$J=B$1)*--(Jan!$J:$J<=B$2),Jan!$J:$J) for multiple sheets? Following formula is not working: =+SUMPRODUCT(--(Jan:Dec!$H:$H=$A3)*--(Jan:Dec!$J:$J=B$1)*--(Jan:Dec!$J:$J<=B$2),Jan:Dec!$J:$J) It is resulting into a #Name? error. Any suggestions? Thanx in advance. Hi, in XL versions before 2007 entire columns like H:H are not allowed. If needed try H1:H65535 or better still, use dynamic references -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'SUMPRODUCT FOR MULTIPLE SHEETS - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=97606) Hi, if you Google around a bit on " sumproduct +3D" you'll find different possible solutions or download the morefunc.xll add-in -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97606 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT FOR MULTIPLE SHEETS
In article ,
FARAZ QURESHI wrote: How can I use a formula like: =SUMPRODUCT(--(Jan!$H:$H=$A3)*--(Jan!$J:$J=B$1)*--(Jan!$J:$J<=B$2),Jan!$J:$J) for multiple sheets? Following formula is not working: =+SUMPRODUCT(--(Jan:Dec!$H:$H=$A3)*--(Jan:Dec!$J:$J=B$1)*--(Jan:Dec!$J:$J<=B$ 2),Jan:Dec!$J:$J) It is resulting into a #Name? error. Any suggestions? Thanx in advance. Download and install the free add-in, Morefunc.xll. Then try... =SUMPRODUCT(--(THREED(Jan:Dec!$H$2:$H$100)=$A3),--(THREED(Jan:Dec!$J$2:$J $100)=B$1),--(THREED(Jan:Dec!$J$2:$J$100)<=B$2),THREED(Jan:Dec! $J$2:$J$1 00)) Adjust the range, accordingly. Note that unless you're using Excel 2007, SUMPRODUCT will not accept whole column references. The add-in can be downloaded at the following link... http://xcell05.free.fr/morefunc/english/ Without the add-in, assuming that Column H contains text values and Column J contains numerical values, try... 1) First define the following... Insert Name Define Name: Column_H Refers to: =T(OFFSET(INDIRECT("'"&TEXT(DATE(2009,{1,2,3,4,5,6 ,7,8,9,10,11,12},1),"mm m")&"'!H2:H100"),ROW(INDIRECT("2:100"))-2,0,1)) **Note that if Column H contains numerical values, replace the first 'T' with 'N'. Click Add Name: Column_J Refers to: =N(OFFSET(INDIRECT("'"&TEXT(DATE(2009,{1,2,3,4,5,6 ,7,8,9,10,11,12},1),"mm m")&"'!J2:J100"),ROW(INDIRECT("2:100"))-2,0,1)) Click Ok 2) Then try... =SUMPRODUCT(--(Column_H=$A3),--(Column_J=B$1),--(Column_J<=B$2),Column_J ) -- Domenic http://www.xl-central.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter | Excel Worksheet Functions | |||
Sumproduct on multiple sheets...please help??? | Excel Worksheet Functions | |||
sumproduct for multiple sheets | Excel Worksheet Functions | |||
sumproduct looking at multiple sheets | Excel Worksheet Functions | |||
sumproduct from multiple sheets | Excel Worksheet Functions |