Tracking salary overall versus by month per person

Options

I have a budget sheet that tracks the overall salary spent on each staff. I have a column formula that can search by name to give me the total amount spent on that staff to date ( =SUMIF([Areas of Project Spending]:[Areas of Project Spending], PERSONNEL@row, Spent:Spent)).

What I need now is a new column that will total salary for the month only by staff name - how do I add that to this formula (or think it needs to be a SUMIF) to get it to total by the current month only? FYI - I do have a date column (Expense Date) for when the salary posts each month.

Thank you wonderful people of SS!

Angela

• ✭✭✭✭✭✭
Options

You don't need the helper column. You can use somethign like 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())))

• ✭✭✭✭✭✭
Options

You can use a Sumifs formula to add the month criteria to the second column.

=SUMIFS(Spent:Spent,[Areas of Project Spending]:[Areas of Project Spending], PERSONNEL@row,{Expense Date]:[Expense Date],>put first date of the month here, {Expense Date]:[Expense Date], <put last date of the month here)

If you want it to automatically update each month to reflect current month I would recommend creating a helper column =Month([Expense Date]@row) and change the formula for your sumifs to the below

=SUMIFS(Spent:Spent,[Areas of Project Spending]:[Areas of Project Spending], PERSONNEL@row, [Month helper column]:[Month helper column],Month(Today()))

• Options

Thank you - I tried the second option as I want the automated result each month but got an error in the helper column.

With the helper column - should that result just be the current month ie - February or 2/23 (or 2/1/23-2/28/23) to make the other formula search for dates in my Expense Date column to sum my Spents?

• ✭✭✭✭✭✭
Options

You don't need the helper column. You can use somethign like 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())))

• Options

Excellent - this worked perfectly!

Thank you,

Angela

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!