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.

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

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

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

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

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