SUMIFS/COUNTIFS Date Issue
Hi all,
I am attempting to create a formula to add up all the Employees from a sheet from last month. This sheet is pulling in totals on an automation monthly. The formula works except the Month section keeps making it 0 when I see it should be a 1. I have a similar issue with a COUNTIF formula.
Can someone tell me the correct way to write this month Piece so it only pulls totals from last month and then how I would right it to pull totals from this month?
The {Historic Month} and the {Term from Roster} columns are Date Only.
=SUMIFS({Historic Totals}, {Historic Month}, MONTH(TODAY(-1)), {Historic Markets}, Market@row - TOTAL@row)
=COUNTIFS({Terminated ES}, "Promoted/Transfer", {Terminated Market}, Market@row, {Term from Roster}, =MONTH(TODAY()))
Thank you!
Best Answer
-
It seems that you compare a whole date vs a month
{Term from Roster} is a date
MONTH(TODAY())-1 is a number from 0 to 11
You should compare MONTH({Term from Roster}) to MONTH(TODAY()-1)
Also, MONTH(TODAY(-1)) is yesterday's month. not last month.
Also, make sur you catch the case Month(Today())-1 =0
Try this:
=SUMIFS({Historic Totals}, Month({Historic Month}), if(MONTH(TODAY()-1))=0,12,MONTH(TODAY()-1))), {Historic Markets}, Market@row - TOTAL@row)
=COUNTIFS({Terminated ES}, "Promoted/Transfer", {Terminated Market}, Market@row, Month({Term from Roster}), =MONTH(TODAY()))
Answers
-
It seems that you compare a whole date vs a month
{Term from Roster} is a date
MONTH(TODAY())-1 is a number from 0 to 11
You should compare MONTH({Term from Roster}) to MONTH(TODAY()-1)
Also, MONTH(TODAY(-1)) is yesterday's month. not last month.
Also, make sur you catch the case Month(Today())-1 =0
Try this:
=SUMIFS({Historic Totals}, Month({Historic Month}), if(MONTH(TODAY()-1))=0,12,MONTH(TODAY()-1))), {Historic Markets}, Market@row - TOTAL@row)
=COUNTIFS({Terminated ES}, "Promoted/Transfer", {Terminated Market}, Market@row, Month({Term from Roster}), =MONTH(TODAY()))
-
@Christian G. Thank you for the assistance! Your formula helped get me almost all the way there but I needed to account for the previous year being looked at in certain instances for Janurary.
Final Formulas ended up being this..
=TOTAL@row - IF(MONTH(TODAY()) = 1, SUMIFS({Historic Totals}, {Historic Month}, IFERROR(MONTH(@cell), 0) = 12, {Historic Month}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1, {Historic Markets}, Market@row), SUMIFS({Historic Totals}, {Historic Month}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Historic Month}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Historic Markets}, Market@row))
=IF(MONTH(TODAY()) = 1, COUNTIFS({Term Create Date}, IFERROR(MONTH(@cell), 0) = 12, {Term Create Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1, {Terminated ES}, "Promoted/Transfer", {Terminated Market}, Market@row), COUNTIFS({Term Create Date}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Term Create Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Terminated ES}, "Promoted/Transfer", {Terminated Market}, Market@row))
-
@Dakota Haeffner Great ! congratulation on finding the right syntaxt. I'm glad I could help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!