How to Sum rows in a column based on date

I have a sheet set-up with distinct numbers project numbers in the rows with columns containing the months of the year. Each month I drop in actual activity for the current month and someone will forecast their spend for the remaining months of the year. Is there a way to have a formula that adds up the columns with actual activity and another formula would sum the remainder of the year. For example I have actuals for January - March which I would like to sum with April - December summed together in another formula. Next month I would drop in April actuals and I would want the formulas to add up January - April and May-December. In Excel I would use the SUM and OFFSET formulas with an input setup to identify the month. Is this possible in SmartSheets?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    In that case, we can utilize it. We don't need to remove it or add in another.


    In each of the month columns, manually enter the month numbers.

    1 - 2 - 3 - 4 - so on and so forth.


    Then to get the actuals you would use:

    =SUMIFS(January@row:December@row, January$1:December:1, @cell< MONTH(TODAY()))


    And for the forecast:

    =SUMIFS(January@row:December@row, January$1:December:1, @cell>= MONTH(TODAY()))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!