I am attempting to use the sumproduct function to determine the number of
ovetime hours worked by week. The following formula is returning a zero
value, which is incorrect:

=SUMPRODUCT((D$2:D$15000="20051009")*(E$2:E$15000= "L102")*(I2,I15000))

Seems pretty straightforward, but it's not working. Any help would be
appreciated.

Replace:
I2,I15000
with
I2:I15000
--
Gary's Student


"Patricia" wrote:

I am attempting to use the sumproduct function to determine the number of
ovetime hours worked by week. The following formula is returning a zero
value, which is incorrect:

=SUMPRODUCT((D$2:D$15000="20051009")*(E$2:E$15000= "L102")*(I2,I15000))

Seems pretty straightforward, but it's not working. Any help would be
appreciated.

Thank's, but that is just a typo. I have typed it as you indicated in my
spreadsheet. Any other suggestions????

"Gary''s Student" wrote:

Replace:
I2,I15000
with
I2:I15000
--
Gary's Student


"Patricia" wrote:

I am attempting to use the sumproduct function to determine the number of
ovetime hours worked by week. The following formula is returning a zero
value, which is incorrect:

=SUMPRODUCT((D$2:D$15000="20051009")*(E$2:E$15000= "L102")*(I2,I15000))

Seems pretty straightforward, but it's not working. Any help would be
appreciated.

: =SUMPRODUCT((D$2:D$15000="20051009")*(E$2:E$15000= "L102")*(I2,I15000))

Have you already tried this way?:

=SUMPRODUCT((D$2:D$15000=20051009)*(E$2:E$15000="L 102")*(I2:I15000))

Roberto

What's in column D?

Is it a date formatted to look like yyyymmdd (or possibly yyyyddmm)???

Are your really comparing column E to the string "L102" or are you trying to
compare column E to what's in cell L102?

I like this format:

=sumproduct(--(D$2:D$15000=date(2005,10,9)),--(E$2:E$15000=L102),(I$2:I$15000))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html




Patricia wrote:

I am attempting to use the sumproduct function to determine the number of
ovetime hours worked by week. The following formula is returning a zero
value, which is incorrect:

=SUMPRODUCT((D$2:D$15000="20051009")*(E$2:E$15000= "L102")*(I2,I15000))

Seems pretty straightforward, but it's not working. Any help would be
appreciated.

--

Dave Peterson

Patricia,

If you have 20051009 in a cell then that is a number and you are looking in
the SUMPRODUCT() for a string - "20051009"

Try removing the quotes within SUMPRODUCT() or format column D as text
*before* you enter 20051009 or preceed with an apostrophe.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Patricia" wrote in message
...
Thank's, but that is just a typo. I have typed it as you indicated in my
spreadsheet. Any other suggestions????

"Gary''s Student" wrote:

Replace:
I2,I15000
with
I2:I15000
--
Gary's Student


"Patricia" wrote:

I am attempting to use the sumproduct function to determine the number
of
ovetime hours worked by week. The following formula is returning a
zero
value, which is incorrect:

=SUMPRODUCT((D$2:D$15000="20051009")*(E$2:E$15000= "L102")*(I2,I15000))

Seems pretty straightforward, but it's not working. Any help would be
appreciated.