Try this...

Create these defined names...

InsertNameDefine
Name: Array1
Refers to:
=COLUMN(INDIRECT("A:AE"))

Name: Array2
Refers to:
=ROW(INDIRECT("1:60"))

Then use this formula:

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&Array1&"'!E1:E10"),Array2-1,,))<""),--(T(OFFSET(INDIRECT("'"&Array1&"'!I1:I10"),Array2-1,,))=E4))

--
Biff
Microsoft Excel MVP


"chrisk" wrote in message
...
I've read through loads of the examples but cant seem to get them to work.
Column E has text (a job) column I has a name (JW).
there are 31 sheets (one per day) in the workbook.
On sheet 32 (just added) i want to total how many jobs JW has done in the
month.
Tried using
=SUMPRODUCT(--('1:31'!E1:E60="*"),--('1:31'!I1:I60="JW"))
but it keeps changing to
=SUMPRODUCT(--('1:[31]31'!E1:E60="*"),--('1:[31]31'!I1:I60=E4))
how do I get around this?
Thanks