SUMIFS/COUNTIFS Date Issue

Dakota Haeffner

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!

Christian G.

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.

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

Dakota Haeffner

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

Christian G.

@Dakota Haeffner Great ! congratulation on finding the right syntaxt. I'm glad I could help.