LinkBack URL
About LinkBacks
I am building a running log for myself and would like to have a table showing my total distances week-to-date (Sunday = day 1), month-to-date, and year-to-date totals.
See yellow highlighted cells in attached spreadsheet.
I am building a running log for myself and would like to have a table showing my total distances week-to-date (Sunday = day 1), month-to-date, and year-to-date totals.
See yellow highlighted cells in attached spreadsheet.
Hi tania_del,
Look at a Pivot Table solution on sheet 2. You can group the dates by days or months. You can also expand or collapse the dates. See what you think.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Not exactly what I had in mind. I was hoping that the WTD, MTD, and YTD totals would automatically be adjusted based on the current date - I am looking for the right formula that will do this for me. So that when I open my spreadsheet, I can see my mileage so far for the week, so far for the month, etc. without having to make selections or update formulas.
If the SUMIF function allowed you to sum values in one range that meet more than one criteria (different criteria being TODAY, TODAY-1, TODAY-2, TODAY-3, etc. But I don't think this is possible and don't know of a formula that can do this.
You can use SUMIFS - syntax wise:
Given the layout you could also just use basic SUMs and use INDEX w/MATCH (based on dates) or OFFSET to identify start/end positions of each range but the above is perhaps simplest.
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I'll be off line for a bit so in case you want or need the others
If you assume you will never have values beyond today then MTD & YTD could be SUMIF (the tests re: <=H3 aren't necessary)Please Login or Register to view this content.
Thanks! It looks like this will do the trick.
@DonkeyOte - If you're still out there, could you please explain how the YTD code above works?
This is an old thread, and you should really start your own thread, but seeing as you are only asking how that formula worked...
=SUMIFS($F$4:$F$368,$B$4:$B$368,">"&EOMONTH($H$3,-MONTH($H$3)),$B$4:$B$368,"<="&$H$3)
Did not look at any file of info, but this is what I make of it.
=SUMIFS($F$4:$F$368,$B$4:$B$368,">"&EOMONTH($H$3,-MONTH($H$3)),$B$4:$B$368,"<="&$H$3)
this would be the range to sum
=SUMIFS($F$4:$F$368,$B$4:$B$368,">"&EOMONTH($H$3,-MONTH($H$3)),$B$4:$B$368,"<="&$H$3)
The trick here is the EOMONTH() function. It is taking the date that is "X" months before the date in H3 - the "X" is being calc'd by determining the month number in H3
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
What had me confused was the criteria, but if, with what you wrote, I understand correctly, presuming H3 is "today's" date, the second criteria put an "end" limit on the accumulation and the portion that readscreates the start.Please Login or Register to view this content.
Isn't there an easier way to specify the first day of the current year of the date being examined? Why not?Please Login or Register to view this content.
As it took some determination to come up with what was used I have to believe with good reason, so I would like to learn from it!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks