Need formula to do more

Hello Super Awesome SS community!

I need help editing a formula I got from the community recently to make it do two more similar tasks. The current formula looks at monthly salaries (areas of spending) by last name and expense date columns that tell me how much was spent in the current month only.

I need something similar that will tell me 1) the total annually by different years ie 2023, 2022, 2021 per name and 2) it needs to be within different time periods, for instance, some will search from May 2022 to April 2023 or Sept 2021 to Aug 2022.

The formula I use now for monthly totals is this: =SUMIFS(Spent:Spent, [Areas of Project Spending]:[Areas of Project Spending], @cell = PERSONNEL@row, [Expense Date]:[Expense Date], AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

I will still be using the Expense Date column to find the dates which look like this in our reports: 1/22/23, 6/20/22 etc.

Any help will be greatly appreciated!

Angela

Best Answer

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

    The bold portion is the area where the changes will need to be made.

    =SUMIFS(Spent:Spent, [Areas of Project Spending]:[Areas of Project Spending], @cell = PERSONNEL@row, [Expense Date]:[Expense Date], AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))


    IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())

    changes to

    @cell>= DATE(yyyy, mm, dd), @cell<= DATE(yyyy, mm, dd)


    You would replace each yyyy with the appropriate year, mm with the month number, and dd with the appropriate day number.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!