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
-
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!
-
@Jeff Reisman -trying to add the same whole column up each day
-
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!
-
@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
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!