Need to get a stacked formula

Hi everyone - please excuse me if I type this incorrectly, this is my 1st time attempting to get assistance

I have the following formula that appears to be working:

=IF(ISDATE([executed date]@row), IF(YEAR([executed date]@row) = 2021, SUMIFS([amount nte]:[amount nte], [formula - Year]:[formula - Year], "2021")))

I am attempting to get the total sales of my "amount nte" column based upon the year the order was entered "executed date" using the following columns:

Number / Executed Date / Amount NTE / Year (formula field - based off of the executed date column)

How do I stack the formula, I can get the data for 2021 but not 2022 and 2023?

Please HELP!


  • Alpha Chucky
    Alpha Chucky ✭✭✭✭

    Hi Shellee, The important aspect to consider is that if you're applying this formula in a row, the formula will only return the total for the year corresponding to the "Executed Date" in that specific row. This approach might not provide you with a summary or total for all years unless applied in a summary row or calculated field.

    However, i think the stacked formula is something like the following:

    =IF(ISDATE([Executed Date]@row), 

      IF(YEAR([Executed Date]@row) = 2021, 

        SUMIFS([Amount NTE]:[Amount NTE], [Formula - Year]:[Formula - Year], "2021"),

      IF(YEAR([Executed Date]@row) = 2022, 

        SUMIFS([Amount NTE]:[Amount NTE], [Formula - Year]:[Formula - Year], "2022"),

      IF(YEAR([Executed Date]@row) = 2023, 

        SUMIFS([Amount NTE]:[Amount NTE], [Formula - Year]:[Formula - Year], "2023")



    What I think is a better approach is to create a lookup table/ summary sheet. You can create a Pivot Report, but i think there is more control if you use a lookup table sheet.

    Your formula is simply: =SUMIF({YearRef}, [Primary Column]@row, {AmountRef})

    see the attached screen shots.

    Projects Delivered. Data Defended.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!