SUMIFS/COUNTIFS Date Issue

Options

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!

Tags:

Best Answer

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    edited 11/02/22 Answer ✓
    Options

    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

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    edited 11/02/22 Answer ✓
    Options

    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
    Dakota Haeffner ✭✭✭✭✭
    Options

    @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.
    Christian G. ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!