Need formula to do more

Angela Hollingsworth

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

Paul Newcome

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.

Paul Newcome

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.

Angela Hollingsworth

Thank you so much Paul - this is exactly what I needed and I was able to figure out another version I needed based on this advice! Love the SS Community!

Paul Newcome

Happy to help. 👍️