Sum cost in row based on current date

ShannonL ✭✭✭
edited 07/02/24 in Formulas and Functions

I have a sheet that contains costs for various projects spread out across multiple quarters for 2 years. I'm trying to find a formula that will calculate [Billed to Date] in each row based on the current date. See attached example sheet.

Essentially the cells in the [Billed to Date] column will update automatically each quarter to sum the cost in the quarters prior to the current date. For example, based on todays date of July 2, 2024, [Billed to date] would show a number that's the sum of costs in [24Q1] and [24Q2] for each row. I added text dates in Row 1 just to clarify what ending date each column represents on the calendar.

The issue I see is that the columns 24Q1, 24Q2, 24Q3, etc… cannot be date formatted because they contain number values. I would also need to account for summing costs across multiple years not, just a single year.

Any suggestions on formulas or helper columns to achieve this is appreciated. Thanks.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!