Tracking salary overall versus by month per person

Angela Hollingsworth

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

Paul Newcome

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())))

Hollie Green

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()))

Angela Hollingsworth

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?

Paul Newcome

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())))

Angela Hollingsworth

Excellent - this worked perfectly!

Thank you,

Angela

Paul Newcome

Happy to help. 👍️