+ Reply to Thread
Results 1 to 7 of 7

Sum By Month/Week/Etc. to Date in a Log

  1. #1
    Registered User
    Join Date
    10-24-2009
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Sum By Month/Week/Etc. to Date in a Log

    Hey. Per doctor's orders I am on a very strict diet and have to monitor my calories/cholesterol/sodium etc.

    I am trying to use Excel to streamline this process. I have a log sheet where I insert rows (so the newest entry is at the top) and fill them in with what I ate, when, how many calories, etc.

    I want to have another sheet which summarizes the totals by either today/week-to-date/month(30 days)-to-date. This would greatly help me always quickly know where I am at, but I am having trouble with figuring out how to sum those 3 things.

  2. #2
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Sum By Month/Week/Etc. to Date in a Log

    attatch file may be helful
    Attached Files Attached Files
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Sum By Month/Week/Etc. to Date in a Log

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

  4. #4
    Registered User
    Join Date
    10-24-2009
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Sum By Month/Week/Etc. to Date in a Log

    I've attached a dummy workbook as recommended. You're right about that being helpful because Azam's file does the right thing, but I don't understand the formula itself, so I can't reconstruct it to fit the structure of my data.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: Sum By Month/Week/Etc. to Date in a Log

    Azam's equations are based off of a hidden date field within the spreadsheet that he posted. The sunproduct equation is basically a summing under certain conditions. The conditions that it's searching for is dependent on that calculated date based on current week, current month, etc.

    If you unhide column B, and copy the date and sumproduct equations that Azam wrote, it should be as simple as altering the range to get it working for you.
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  6. #6
    Registered User
    Join Date
    10-24-2009
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Sum By Month/Week/Etc. to Date in a Log

    I figured it out with everyone's help still, I used the SUMIF('log date range',today,'log cal column').

    I have one more question which may be a little more difficult. For today's values, my
    calories include something I ate after midnite, which technically is correct. Can I change that to modify only things eaten after 7 AM? Here is my current formula for reference:

    =SUMIF(Log!B1:Log!B1001,B3,Log!H1:Log!H1001)

    ** where B3 is the =TODAY() field, Log!B is the date column and Log!H is the calories column.

  7. #7
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Sum By Month/Week/Etc. to Date in a Log

    the calculation on the basis of time are also possible if you also enter the time in your log sheet.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1