Sumif comparing dates in criteria
in [General]
|
Prev: z-order of various lines and bars in an Excel 2007 chart
Next: Name a worksheet tab based on the value in a cell.
From: sdjsage on 2 Apr 2010 13:19 I'm trying to use SUMIF =SUMIF(L6:L8,"=and(>B20,<=C20)",K6:K8) where column L has dates in it, column K has dollar amounts and the criteria is looking at a fields that have dates. So, trying to include in the sum the payment amount if the estimated payment date is greater than last week's date but less then the current week's date. Working on a cash flow tool that would read from a list of all of our contracts entered and their estimated payment dates.
From: Roger Govier on 2 Apr 2010 13:37 Hi Try =SUMPRODUCT(($L$6:$L$8>=B20)*($L6:$L8<=C20)*K6:K8) -- Regards Roger Govier sdjsage wrote: > I'm trying to use SUMIF > =SUMIF(L6:L8,"=and(>B20,<=C20)",K6:K8) > where column L has dates in it, column K has dollar amounts and the criteria > is looking at a fields that have dates. > So, trying to include in the sum the payment amount if the estimated payment > date is greater than last week's date but less then the current week's date. > > Working on a cash flow tool that would read from a list of all of our > contracts entered and their estimated payment dates. > > >
From: Paul on 2 Apr 2010 13:35
SUMIF can only have one criteria, but you're looking to use two (less than or equal, greater than). In 2007 you can use SUMIFS, but if other users on 2003 need to use the sheet, then try SUMPRODUCT: =SUMPRODUCT((L6:L8>B20)*(L6:L8<=C20)*K6:K8) -- Paul - Paul ------------------------------------------------------------------------ Paul's Profile: 1697 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192562 http://www.thecodecage.com/forumz |