Cumulative sum using SUMIFS

I am trying to calculate a cumulative sum or running total with a sumifs formula. The below is working, but I imagine there is an easier way to do this formula.

Below is the formula I have in Q1 '23 Forecast and I keep adding a new sumifs formula as I go down the row of forecast columns.

=SUMIF($Category$3:$Category$14, $Category@row, $[Actuals (period to date)]$3:$[Actuals (period to date)]$14) + SUMIF($Category$3:$Category$14, $Category@row, [Q1 '23 Forecast]3:[Q1 '23 Forecast]14)



Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    Unfortunately I don't think there's a much faster way to do what you want to do. You could add a helper column with a column formula that sums the Actuals+Qx Forecast for each quarter, then simply SUMIF that column instead. But that seems like potentially more work than just copying and adjusting the formula you already have.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!