SUMPRODUCT OR COUNTIF
 

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

SUMPRODUCT OR COUNTIF
 

Chris,

You need to use THREED('1:31'!E1:E60) in your SUMPRODUCT formula. THREED
isn't standard and you need to get it as an add-in. I believe it's Morefunc
available at http://xcell05.free.fr/

v/r
--
Brian


"chrisk" wrote:

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

SUMPRODUCT OR COUNTIF
 

Crashed with 'Fatal Error' when I tried to download it.

"Brian" wrote:

Chris,

You need to use THREED('1:31'!E1:E60) in your SUMPRODUCT formula. THREED
isn't standard and you need to get it as an add-in. I believe it's Morefunc
available at http://xcell05.free.fr/

v/r
--
Brian


"chrisk" wrote:

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

SUMPRODUCT OR COUNTIF
 

Here's another site.

http://www.download.com/Morefunc/300...-10423159.html

--
Brian


"chrisk" wrote:

Crashed with 'Fatal Error' when I tried to download it.

"Brian" wrote:

Chris,

You need to use THREED('1:31'!E1:E60) in your SUMPRODUCT formula. THREED
isn't standard and you need to get it as an add-in. I believe it's Morefunc
available at http://xcell05.free.fr/

v/r
--
Brian


"chrisk" wrote:

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

SUMPRODUCT OR COUNTIF
 

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

SUMPRODUCT OR COUNTIF
 

Ooops!

I used the wrong range sizes in the formula, E1:E10 and I1:I10.

Change those to E1:E60 and I1:I60.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

SUMPRODUCT OR COUNTIF
 

Hi "T. Valko"

It sort of worked.
It definatly counted something, but less than the jobs that they have done.
I'd rather a solution along these lines rather than a download.

Thanks