Adding in a Date Range

  • Thread starter Thread starter readystate
  • Start date Start date
R

readystate

I have a worksheet with a Date Column. I want to include a formula that will
add, for example, amounts for the month of February 2008. How would I set
that up? Thanks in advance.
 
Assume your dates are in column A and you want to add amounts from
column B - assume 1000 rows are used. To make it more flexible, put
the date you are searching for in C1 (formatted as mmmm yyyy - the day
doesn't matter), then put this formula in D1:

=SUMPRODUCT((MONTH(A$1:A$1000)=MONTH(C1))*(YEAR(A$1:A
$1000)=YEAR(C1))*(B$1:B$1000))

Hope this helps.

Pete
 
Hi,

When summing in this way it's a good idea to keep the date to sum outside of
the formula so with 1/2/2008 in C1 the day of the monthe can be any

=SUMPRODUCT(--(YEAR(A1:A20)=YEAR(C1)),--(MONTH(A1:A20)=MONTH(C1)),B1:B20)

This will add up coolumn B for every Feb 2008 in column A1 - A20

Mike
 
Thanks...worked great!!!

Mike H said:
Hi,

When summing in this way it's a good idea to keep the date to sum outside of
the formula so with 1/2/2008 in C1 the day of the monthe can be any

=SUMPRODUCT(--(YEAR(A1:A20)=YEAR(C1)),--(MONTH(A1:A20)=MONTH(C1)),B1:B20)

This will add up coolumn B for every Feb 2008 in column A1 - A20

Mike
Click to expand...
 
Hi

One way, assuming your dates are in column A and values in column B

=SUMPRODUCT((TEXT($A$1:$A$1000,"yymm")="0802")*($B$1:$B$1000))
 
Back
Top