+ Reply to Thread
Results 1 to 5 of 5

Calculate totals based on date

  1. #1
    Registered User
    Join Date
    01-11-2004
    Posts
    22

    Calculate totals based on date

    Good day everyone. This forum has become invaluable in my education of Excel. I now have a question I don't know the answer to.

    I am trying to create a spreadsheet that adds up a progressive total of tasks done compared to what should be done by that date. For example if today is January 3rd I want the "should be done" total to be a cumulative total of Jan 1, 2 and 3. However, I don't want it to add up the information for the rest of the year in the succeeding cells. Basically I want it to automatically update the formula to cumulate the total based on what the date is.

    I have attached a PDF that illustrates what I am looking to do. This is a sample and my actual file has the dates filled to the end of the year.

    All help would be most appreciated.

    Thank you

    Shane
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Quote Originally Posted by shane24
    Good day everyone. This forum has become invaluable in my education of Excel. I now have a question I don't know the answer to.

    I am trying to create a spreadsheet that adds up a progressive total of tasks done compared to what should be done by that date. For example if today is January 3rd I want the "should be done" total to be a cumulative total of Jan 1, 2 and 3. However, I don't want it to add up the information for the rest of the year in the succeeding cells. Basically I want it to automatically update the formula to cumulate the total based on what the date is.

    I have attached a PDF that illustrates what I am looking to do. This is a sample and my actual file has the dates filled to the end of the year.

    All help would be most appreciated.

    Thank you

    Shane
    try this

    =SUMIF(A2:A22,"<="&TODAY(),B2:B22)

    Regards.

  3. #3
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by shane24
    Good day everyone. This forum has become invaluable in my education of Excel. I now have a question I don't know the answer to.

    I am trying to create a spreadsheet that adds up a progressive total of tasks done compared to what should be done by that date. For example if today is January 3rd I want the "should be done" total to be a cumulative total of Jan 1, 2 and 3. However, I don't want it to add up the information for the rest of the year in the succeeding cells. Basically I want it to automatically update the formula to cumulate the total based on what the date is.

    I have attached a PDF that illustrates what I am looking to do. This is a sample and my actual file has the dates filled to the end of the year.

    All help would be most appreciated.

    Thank you

    Shane
    HI,

    =SUMPRODUCT(--(A1:A100<TODAY())*(--(A1:A100<>"")*(B1:B100)))

    should total the B column for you, two other copies of that for C1:C100 and D1:D100 should do the rest, however, the range 1:100 will need to be set to suit your data.

    hth
    ---
    Si fractum non sit, noli id reficere.

  4. #4
    Registered User
    Join Date
    01-11-2004
    Posts
    22
    You guys are wonderful and fast to respond. Just great I tell ya.

    I have posted a sample of how I plugged in the formulas that each of you have provided. One seems to work and the other doesn't. If you have a moment please take a look and provide feedback.

    Thanks,
    Shane
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Quote Originally Posted by shane24
    You guys are wonderful and fast to respond. Just great I tell ya.

    I have posted a sample of how I plugged in the formulas that each of you have provided. One seems to work and the other doesn't. If you have a moment please take a look and provide feedback.

    Thanks,
    Shane
    Bryan's formula will work with a little change.

    =SUMPRODUCT(--(A1:A100<=TODAY())*(--(A1:A100<>"")*(B1:B100)))

+ 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