Formula to sum cells within date range.

[jxpresto]

Good morning! This is my first post here and I am hoping to get some help with a formula that has me stumped. Usually I am pretty good with searching the web for help with Excel but I can't seem to find what I need for this one.

Here is a summary of what I am looking for. Let's say I have the following grid of data:

Date	Numeric Value
12/1/2012	3
6/5/2012	2
1/23/2012	2
1/1/2012	5

I am looking for a formula that will sum the values in "Numeric Value" only if the date falls somewhere between the current date minus 12 months (or 365 days, whichever is easier). So assuming today's date is 1/22/2013, I would want to see a result of 7. However, come the end of this week, I would want the result to be 5 because the 1/23/2012 date should now fall outside of that range.

It is worth mentioning that this grid will expand over time as new lines are added. I am open to creating a number of blank rows to populate later if that is necessary.

I greatly appreciate any help that you can offer. Please let me know if I need to clarify anything further.

[Melvinrobb]

Assuming your table starts in A1, the following should work:

Please Login or Register  to view this content.

[benishiryo]

hi jxpresto, welcome to the forum. try:
=SUMPRODUCT((A2:A5>=TODAY()-365)*(A2:A5<=TODAY()),B2:B5)

or:
=SUMPRODUCT((A2:A5>=TODAY()-DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))*(A2:A5<=TODAY()),B2:B5)

you can expand the range if you are expecting more data

[Tony Valko]

Try this...

=SUMPRODUCT(--(A2:A100>=TODAY()-365),--(A2:A100<=TODAY()),B2:B100)

Let's go Pens!

[Fotis1991]

=sumproduct((a1:a10>=today()-365)*(b1:b10))

[jxpresto]

Wow thank you all for the prompt replies, the suggestions worked perfectly.

[vlady]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.