SUMPRODUCT OR COUNTIF

  • Thread starter Thread starter chrisk
  • Start date Start date
C

chrisk

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
 
Crashed with 'Fatal Error' when I tried to download it.

Brian said:
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 said:
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
Click to expand...
Click to expand...
 
Here's another site.

http://www.download.com/Morefunc/3000-2077_4-10423159.html

--
Brian


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

Brian said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Try this...

Create these defined names...

Insert>Name>Define
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))
 
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 said:
Try this...

Create these defined names...

Insert>Name>Define
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 said:
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
Click to expand...
Click to expand...
 
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
 
Back
Top