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