Sum of column by date

Allison Crull

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

Jeff Reisman

@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?

Allison Crull

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317010#Comment_317010

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.

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

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.

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: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

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!