SUMPRODUCT FOR MULTIPLE SHEETS

[FARAZ QURESHI]

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(--(Janec!$H:$H=$A3)*--(Janec!$J:$J>=B$1)*--(Janec!$J:$J<=B$2),Janec!$J:$J)
It is resulting into a #Name? error.
Any suggestions?
Thanx in advance.

 

[Pecoflyer]

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(--(Janec!$H:$H=$A3)*--(Janec!$J:$J>=B$1)*--(Janec!$J:$J<=B$2),Janec!$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

 

[David Biddulph]

.... and, of course, there are various unnecessary characters in the formula.

=+SUMPRODUCT(...) is the same as =SUMPRODUCT(...)
and ...*--(...) is the same as ...*(...)

 

[FARAZ QURESHI]

XL 2007 is the version being used & the removal of unnecessary characters
also doesn't help, i.e.:

=SUMPRODUCT((Janec!$H1:$H10000=$A289)*(Janec!$J1:$J10000>=B$1)*(Janec!$J1:$J10000<=B$2),Janec!$J1:$J10000)

is still not working. Any idea?

 

[Pecoflyer]

XL 2007 is the version being used & the removal of unnecessary
characters
also doesn't help, i.e.:

=SUMPRODUCT((Janec!$H1:$H10000=$A289)*(Janec!$J1:$J10000>=B$1)*(Janec!$J1:$J10000<=B$2),Janec!$J1:$J10000)

is still not working. Any idea?

Hi,
if you Google around a bit on " sumproduct +3D" you'll find different
possible solutions or download the morefunc.xll add-in

 

[Domenic]

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(--(Janec!$H:$H=$A3)*--(Janec!$J:$J>=B$1)*--(Janec!$J:$J<=B$
2),Janec!$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(Janec!$H$2:$H$100)=$A3),--(THREED(Janec!$J$2:$J
$100)>=B$1),--(THREED(Janec!$J$2:$J$100)<=B$2),THREED(Janec!$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
)