Sum of column by date

Hello-

I am trying to build a formula that calculates the sum of a column every day.

EX: 1/1/22-gives the sum of the column on that date

1/2/22, give the sum of the column on that date

So we can see how the calculation changes day over day

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/21/22

    @Allison Crull

    What's your data structure like? Is it something like this where you have a date column and a column to sum?


    Or are you just trying to add the same whole column up again each day, and record that somewhere?

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Allison Crull
    Allison Crull ✭✭✭✭

    @Jeff Reisman -trying to add the same whole column up each day

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/21/22

    OK, for this type of thing I recommend using a separate sheet to collect your sum each day.

    Overview: Get Smartsheet to copy one row from your sheet daily to your sum sheet, in order to create a new row in the sum sheet with the current date and that day's sum.

    I'll call the two sheets Data and Sum.

    On your data sheet, create your row where you will add up the column. Put your SUM formula on this row. (If the number of rows you will be adding up each day changes, I would say make the top row of the sheet the TOTAL row.) The SUM formula should NOT be in the column you're adding up each day.

    =SUM([Value Column]:[Value Column])

    Use another column called "Total" to indicate that this is the Total row, by putting "TOTAL" in it, or something like that.

    Create a Date column and enter =TODAY() on the same row as your SUM formula.

    Next, create an automation rule:

    Trigger: 11pm every day

    Condition Block: where the "Total" column has "TOTAL" in it

    Action: Copy row to your Sum sheet.

    What Smartsheet will do is copy the row and its calculated values over to your Sum sheet. So instead of copying the Date column with the formula =TODAY() in it, it will copy the date for the current day. Instead of copying over the SUM formula, it just copies the calculated sum value. The result is that on your Sum sheet, you're left with a static copy of what your TOTAL row was at 11pm, with that day's date on it. The next night, Smartsheet copies the TOTAL row from the Data sheet to a new row on the Sum sheet, with the next Date and a newly calculated Total.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Allison Crull
    Allison Crull ✭✭✭✭

    @Jeff Reisman is there a way to do this without copying rows?

    My original thinking is to add the =Today() to the sheet

    Then have a summary sheet with a formula that can look at the Today column and @row date then sum of column

    This is what I did but it returned a zero value


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    The problem with that is that the formula =TODAY() is not static. It changes every day (provided the sheet it's on is opened that day.) So a formula based on the today function will constantly refer to the current day and change accordingly, which doesn't work if you're trying to record a static value. You could use an automation rule to record the current date in a cell, but you can't use it to change a formula unless your formula is keyed off the value of that cell.

    That being said, your syntax is just out of order in the formula above. The syntax is SUMIF(Range to be summed, Criteria Range, Criteria)

    =SUMIF({Sales Forecast FY22 / Jan Total}, {Sales Forecast FY22/ Today}, Month@row)

    I can't say for sure that will work with knowing more about what's in those sheet references.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Allison Crull

    I hope you're well and safe!

    To add to Jeff's excellent advice/answer.

    This might help!

    Please have a look at my post below with a method I developed.

    More info: 

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!