SUMIF,SUMPRODUCT
 

I have a spread sheet with columns
D= Weekstart (1/1/2006), E=weekend1/7/2006, These are to test a range for
each week and then I=workday 1/3/2006, J=hours (4)

I want to sum all the hours by week into F . All the column data is set up
for the year, but I can't see how to make the formula and test a range of
dates. Do I use sumif or sumproduct?

SUMIF,SUMPRODUCT
 

You could use either

=SUMPRODUCT(--($I$1:$I$100=D1), --($I$1:$I$100<=E1), $J$1:$J$100)

or
=SUMIF($I$1:$I$100, "="&D1, $J$1:$J$100) - SUMIF($I$1:$I$100, ""&E1,
$J$1:$J$100)

change ranges as needed, SUMPRODUCT cannot accomodate an entire column, such
as A:A.


"litngldy" wrote:

I have a spread sheet with columns
D= Weekstart (1/1/2006), E=weekend1/7/2006, These are to test a range for
each week and then I=workday 1/3/2006, J=hours (4)

I want to sum all the hours by week into F . All the column data is set up
for the year, but I can't see how to make the formula and test a range of
dates. Do I use sumif or sumproduct?

SUMIF,SUMPRODUCT
 

You could also use

=SUMIF(I2:I20,"="&E1,J2:J20)-SUMIF(I2:I20,""&E2,J2:J20)

which sums all amount greater than one date and subtracts all amounts . than
the later date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"litngldy" wrote in message
...
I have a spread sheet with columns
D= Weekstart (1/1/2006), E=weekend1/7/2006, These are to test a range
for
each week and then I=workday 1/3/2006, J=hours (4)

I want to sum all the hours by week into F . All the column data is set
up
for the year, but I can't see how to make the formula and test a range of
dates. Do I use sumif or sumproduct?