Tracking salary overall versus by month per person
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
Best Answer
-
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())))
Answers
-
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()))
-
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?
-
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())))
-
Excellent - this worked perfectly!
Thank you,
Angela
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!