Summing cell data based on date ranges?

T

THORmx

New Member
Joined
Nov 9, 2010
Messages
9
Hi,

I'm hoping to somehow to a date look back, from today(), looking back 30, 60, 90, 120, 180, and 365 days. Now, I have all the dates of respective flights in Column A. In Column F I have the total hours for the respective date. I'd somehow like it to basically look at today's date, compare to Column A, figure up to a date X amount of days prior, and add up that many cells from Column F.

Is this even remotely possible? It is helpful as we pilots have to have look backs on all those days when doing job apps and such, and this would simplify a TON for me!

Thanks in advance! http://www.excelforum.com/editpost.php?do=editpost&p=2415042
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Hey there,

Welcome to the board.

Trying to get my head around what you want.

To 'look back x days' you can simply do =DATE()-30 (or 60,90,120 etc).

What do you want to do with this? Can you give me some sample data?
 
Upvote 0
Hi JamesW,

Thanks a lot for the reply. Basically, I need the excel doc to look at today, and look at the dates in Column A, figure out all the instances between today and X amount of days back, and then take the numbers in column F where all those instances in A were, and add them.

I know, it's really confusing. I'll try to attach a sample copy.
 
Upvote 0
Try:

<?XML:NAMESPACE PREFIX = TODAY()),(--(A2 /><TODAY()),(--(A2:A12>
Code: 
=SUMPRODUCT(--(A2:A12< TODAY ( ) ) ,(--(A2:A12>TODAY()-60)),F2:F12)

Where A2:A12 is your date range, TODAY()-60 is your 'today - days back', and F2:F12 is your numbers to add together.

Ignore the spaces in the code, had to do it as it wouldn't post otherwise :P

Is this what you want?
</TODAY()),(--(A2:A12>
 
Last edited:
Upvote 0
Hi James,

Thanks for the reply. That could possibly work. As it stands, I have about 900 rows x 72 columns in so far, and that grows daily.

Something that utilized your code, such as A3:A20000, would probably work, but maybe a pivot table would be used to to sort it? It is complex. Thanks a lot for bearing with me.

Would that work in regards to something with that much data? Such as:

Code: 
=SUMPRODUCT(--(A3:A20000< TODAY ( ) ) ,(--(A3:A20000>TODAY()-60)),F3:F20000)</pre>

I'm trying to attach a sample, is there a way to do that?
 
Last edited:
Upvote 0
Hey Arthur,

Yes, that is my cross post. Are these two forums connected? I wasn't sure, but I figured going in multiple places would be the best way to get an answer.

That post is hard to understand, as everything has since been smoothed over with the moderator royUK. We had a misunderstanding that led to words, but it is all okay. I apologized, and he accepted. You can click the banned name there and look at past posts to see how I normally am.

You can also see my new thread there too. I'm not trying to hide who I am, and the mods there know that also. http://www.excelforum.com/excel-general/752551-sum-cells-from-data-based-on-dates.html

Was it wrong to post on multiple forums? If so, I'm sorry, I apologize :(

Edit: I would have changed the link, but I'm unsure on how to code that.
 
Upvote 0
Hi James,

We are close to this! The 30 day works perfect. However, the 60, 90, 120, 180, 365 are off just a touch.

I guess one thing could be, that it needs to count the current day also. So, the 60 day should be taking today the 9th, and looking 60 back counting today. I guess where I could be getting off on this just a little bit, is that I usually just figure 09 Nov 10, to 09 Sept 10, even though that may be wrong just a bit, as it's not a true 60 days right? So, almost in essence, this is more accurate?

Thanks again!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Conditional format dates in a range which are x days from today
Replies
3
Views
460
Sallyprit
S
P
  • Question
Changing a value based on data in a previous cell/previous tab - looking back 180 days
Replies
2
Views
177
felixstraube
F
S
  • Question
IF cells in column A contains a dates that are 7 days before todays date then output the date and the value in the coulumn next to it in a list
Replies
1
Views
391
etaf
etaf
R
  • Question
Date formula Help!
2
Replies
11
Views
619
Asbestos_Jen
Asbestos_Jen
Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
160
awoohaw
awoohaw

Forum statistics

Threads
1,215,059
Messages
6,122,916
Members
449,093
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top