Formula to sum amounts based on Today() or chosen date

  • I'm looking for the correct formula to do the following:

    For example:
    Column A has dates in it.
    Column B has dollar amounts.

    I want to sum the dollar amounts if the date is before today(), or any other dates I so choose. I would like to use today() in the function so that it can automatically update the sum.

    Please Help!
    Thanks!

  • Re: Help with Excel Formula


    Hello inland,
    1. Before I start, you might want to add a few more keywords to your subject. Perhaps something like "Help with conditional excel formula using today()" or something of the sort. It'll have a greater chance of matching hits in the search and increase the odds of people responding to your query.

    2. Are you looking for a macro that does this sum or just a static formula that resides in a cell? From your description, it sounds like you want a macro ...

    3. You have to be a bit more specific on the date you are looking for. You mention that you want the sum to occur if the date is < then today() or any other date .. we need more specificity (or at least I do) in order to understand your request. I'm guess you are looking for something like:

    Chuck Pitre
    ~ a SIP Pimp ~

  • Re: Help with Excel Formula


    Thank you for your suggestions and help!...

    I'll do my best to explain this:
    I was thinking of a static formula that would automatically update based upon the current date. Essentially I want the formula to check all of the cells in a column. If they are less than todays date (i.e. in the past) I would like them to take the corresponding $$ amounts in the next column and add them up. Basically I want a sum of $$ that is to-date.

    I appreciate your help!
    -Inland

  • Re: Formula to sum amounts based on Today() or chosen date


    Inland,

    Welcome to Ozgrid, please note the change made to your thread title. Your orginal title Help with Excel Formula does not help those trying to provide you with a solution nor will it help others find this thread when it contains a solution. Please take this into consideration when you next post.

    Thank you.

  • Re: Help with Excel Formula


    hrm, I've never done a static formula with this complexity - normally I just use VBA
    So do you want it to add the $$ amounts of all rows with a date < today(), thus leaving out those with a date > today in the (for sake of discussion lets call it..) subtotal?
    OR
    Do you want it to add the $$ amounts of all rows if one of them has a date < today() ?

    The general idea of such a beast looks like this

    =if(LOGIC,TRUE,FALSE)
    =if(A3<today(),(A4+B4),"")


    Though .. now I think you need a sum if condition. So, for this example, A1 -> A10 contain dates, B1 -> B10 contain dollar figures

    =SUM(IF((A1:A10<today()),B1:B10,0))


    hrm... looking at that logic I'm not sure if it will work ... try it and see
    Failing that, off the top of my head, a work around would be to create an extra column. In that column put

    =if(A1<today()),$B$1,0)

    this will copy the dollar amount from the specific cell into the cell from the new column (lets call that column C)
    those that don't match the if criteria will put 0 in the new cell. Then all you need to do is

    =SUM(C1:C10)


    That will sum all of those that match the if criteria. Those that are in a future date, will not sum as their value is 0

    You can also try searching for "nested if statement and sum excel" and see what google brings up

    Cheers

    Chuck Pitre
    ~ a SIP Pimp ~

  • Re: Help with Excel Formula


    Thanks for the reply...

    I've tried these and am stuck because I want the SUMIF to look at column A, and if it is true... use the corresponding numbers in column B as the sum. Unfortunately I do not have room to add another column and keep the functionality of the workbook... so that solution is out.

    *Column A is purely a reference... it does not contain any information that will need to be added.

    Excel is a machine! There's got to be SOME WAY to figure it out!

    Thank you greatly for your help!!! I hope we can find a solution!

    Inland

  • Re: Help with Excel Formula


    Hello inland,
    I'm slightly confused and can't visualize what you are requesting. From your original query, I thought you were looking to have all the $$ that is up to date to be added together (SUM). Is this not what you are looking for? To move forward I would need a visual of your spreadsheet (use dummy data). Otherwise I would suggest using a Macro.

    Chuck Pitre
    ~ a SIP Pimp ~

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!