Need to get a stacked formula

Shellee Gillies

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!

Michael Drayton

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.