SUMPRODUCT
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
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
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
: =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 |
SUMPRODUCT
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 |
SUMPRODUCT
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. |
All times are GMT +1. The time now is 07:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com