Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|