Hello,
I am looking for a way to add a set of number which corresponds to the previous 30 days from the day I am looking at excel.
For example, column A is a list of dates, column B is a number. if A1 is 40 days ago, I do not want B1 to be included in the sum. If A2 is 20 days ago, I want B2 to be included in the sum. I would like the value on the 30th day ago to be included as well.
Also, if it is possible to have another cell to say a phrase if the sum of the cells within 30 days is over a specific number, for example 110.
I tried making a formula, but it did not work:
=SUM(IF(TODAY()-A4:A200-30),SUM(I4:L200))
Thanks in advance.
Hello & Welcome to the Board,
Without seeing your data, maybe this...
=SUMIF(A1:A10,"<"&TODAY()-20,B1:B10)
Last edited by jeffreybrown; 08-20-2011 at 06:35 PM.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
It would be helpful to test this in a sample workbook but, as there isn't one, I'll have to leave you to try it and let me know if it's what you're looking for:
=SUMIF(A:A,"<="&TODAY()-30,B:B)
Regards
Hello,
I have tried both formulas. It comes back with an incorrect response, and will not update when a random value is added in any column. Also, there may be values in multiple columns. I have added a sample workbook where I am trying to have the formula in work.
Thanks again for your help.
You need to add some narrative here.
What is your desire result, why, and from where?
This is your spreadsheet, you understand it, we don't
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Sorry, I did not think of that!
It is a logbook for my hours as a pilot. Every day I fly I write in how many hours I was in the air, and what kind of hours (in command, co-pilot, etc) (H:Q). The date that I flew is in column A.
There are many regulations about how many hours you can fly in a specific time frame, so I am trying to have a formula count the hours for me. For instance, in the previous 30 days, you can have a maximum of 120 hours, so if the date in column A is within the previous 30 days, it should add up any times in the corresponding H to Q column (there may be an entry in H and K for example, both should be added). I would like this number to show up in a box so I can see how close I am to the limit, and hence how much I can legally fly before I am over the limit.
From this I think I can extrapolate to get the other regulations (1200 in 365 days, etc), and then I will put them on a new sheet in the background, and have a cell turn red on the main page saying (for example) "117.5 in 30 days". As far as I know, that would be accomplished with conditional formatting.
Thanks
EDIT: Columns C to F have been erased for privacy reasons, but there is insignificant text there
Last edited by Mattias1; 08-20-2011 at 08:33 PM. Reason: Forgot information.
Here is my suggestion.
In column AB you put a sum for the day and then you can use column AB within the Sumif instead of trying to look at all the columns at once.
Look in column AB and then the formula in D255
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Thank you, but it seems to have a problem. When I add a new line, the value changes right after I put in the date and before I put in a number. I am not sure why this happens.
I would prefer to not have a total column since I may have to print this a few times. I worked around that by adding the sums in a new sheet, however I will be adding to this sheet for years and it would be best to not have to use that background sheet to continuously add more lines.
Also, when I rewrote the formula into my sheet, it did not work properly. I made column A to be the date (=Logbook!A4), and B to be the total (=SUM(Logbook!H4:Logbook!Q4)), and the formula (=SUMIF(A:A,">="&LOOKUP(9.99999999999999E+307,A:A)-30,B:B)) returned 440, when it should be 107.5. I am not sure how LOOKUP works, is the date value built in to this?
Thanks again
Here is another suggestion.
Adding new rows is not all that bad of an idea, but it can cause some extra setup problems. You would have to account for how to copy the formulas down and changing ranges.
Instead of totals at the bottom of the sheet I would change them to the top of the sheet like in the attachment.
In column AB you can drag down the sum as far as you want so all the rows are ready.
Now enter a date in column A and then some hours I249. I entered today's date in A249 and then a 6 in I249 and the 107.5 hours not turns into 113.5
The way the lookup works, it looks down column A and looks for the biggest number and returns that number. If you want to go with =SUMIF(A:A,">="&TODAY()-30,AB:AB) that would be okay, but I figured the last value in colum A would also be today's date.
As far as printing and not getting column AB, hide it or set the print range not to include it.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Oh ok, that works then!
You're correct, the last entry will always be todays date, however if I do not fly a day, the day 30 days ago should drop off the count. I think TODAY() will help that. Thanks a lot for your help.
Side question, I put the AB column on a new sheet, however it looks like it doesn't update when I entered a new line in sheet1. I made it so A4 on sheet1 is A4 on sheet2, and SUM(H4:Q4) is B4 on sheet2. Shouldn't this update automatically when a new entry is added to line 249, nothing happened in sheet2.
I don't see where you are referencing the sheet in the formula.
Example:
=SUM(Sheet1!H4:Q4)
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks