How to find the value of one cell from looking at three columns

miaha
miaha
edited 05/25/23 in Formulas and Functions

Hi everyone I am trying to create a formula where it utilizes three columns to pull a value of one cell from the row.

Ultimately what I am trying to accomplish is create an formula where there are three columns in a report (Year, Month, and Average Amount)

Every month a new row will be added and a formula I am trying to create a formula in a new cell to feed into a dashboard

The Formula will first look for the specific year so for example 2023, and then it will look for the most current month which would be numbered through 1-12. After it finds the row with the most current month it will pull the value from the Average Amount Column every time a new month is added is there a formula that can do this?

This is what I currently have so far

=IF((([year],2023) AND if([month], 11)), [Average_Amount]@[month]row))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If new rows are being added to the bottom of the sheet, you can use:

    =INDEX([Average_Amount]:[Average_Amount], COUNTIFS([Average_Amount]:[Average_Amount], @cell <> ""))

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    So you are going to need a helper cell. I suggest to put it in the summary and call it today and just put this formula in it:

    =TODAY()

    Then you will need to reference that cell in your formula:

    =IF(AND(Year@row="2023",Month@row=MONTH(Summary Cell)),[Average_Amount]@row)

    You don't need brackets around any column that is a single word.

    Not 100% sure that formula will work, but this will get you on a better path.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!