I need to be able to sum one column based on dates in another column. Thankful for any help on this.
G garlocd Jul 11, 2006 #1 I need to be able to sum one column based on dates in another column. Thankful for any help on this.
B Biff Jul 11, 2006 #2 Hi! Try this: D1 = start date E1 = end date Sum values in column B where the corresponding dates in column A fall within the date range (inclusive): =SUMIF(A1:A20,">="&D1,B1:B20)-SUMIF(A1:A20,">"&E1,B1:B20) Or: =SUMPRODUCT(--(A1:A20>=D1),--(A1:A20<=E1),B1:B20) Biff
Hi! Try this: D1 = start date E1 = end date Sum values in column B where the corresponding dates in column A fall within the date range (inclusive): =SUMIF(A1:A20,">="&D1,B1:B20)-SUMIF(A1:A20,">"&E1,B1:B20) Or: =SUMPRODUCT(--(A1:A20>=D1),--(A1:A20<=E1),B1:B20) Biff
M Max Jul 11, 2006 #3 garlocd said: I need to be able to sum one column based on dates in another column. Click to expand... One way .. Assuming real dates are within $A$2:$A$100, with values to be summed in $B$2:$B$100 Try something like this in say, C1: =SUMPRODUCT(($A$2:$A$100>= -- "1-Jun-2006")*($A$2:$A$100<= -- "15-Jun-2006"),$B$2:$B$100) Adapt to suit, but note that entire col references eg: A:A, B:B, cannot be used in SUMPRODUCT
garlocd said: I need to be able to sum one column based on dates in another column. Click to expand... One way .. Assuming real dates are within $A$2:$A$100, with values to be summed in $B$2:$B$100 Try something like this in say, C1: =SUMPRODUCT(($A$2:$A$100>= -- "1-Jun-2006")*($A$2:$A$100<= -- "15-Jun-2006"),$B$2:$B$100) Adapt to suit, but note that entire col references eg: A:A, B:B, cannot be used in SUMPRODUCT
