Need assistance with a formula

Hello,

I am using the below formula to count the number of rows where 140R was checked previous month. This formula was working perfectly fine until this month when the year changed. How can I fix this formula where previous month should be looked at December 2019 for January 2020, but it should look at January 2020 in February 2020?

=COUNTIFS([140R]:[140R], 1, Date:Date, YEAR(@cell) = YEAR(TODAY()), Date:Date, MONTH(@cell) = MONTH(TODAY()) - 1)

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

    The above will give you the last day of the previous month. If you pull the year and month from this date for the year and month for your calculations, you should be good to go.


    =COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I personally would use a slightly different approach, but that really is just personal preference. The only change I would actually suggest to your formula would be adding in the IFERROR portions in case there are issues in the Date column such as text entries or blanks. It isn't really NECESSARY, but it is an added level of security/accuracy.


    IFERROR(YEAR(@cell), 0)

    IFERROR(MONTH(@cell), 0)


    =COUNTIFS([140R]:[140R], 1, Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Yes.


    DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

    will give you the first day of the current month.


    When you subtract 1 from that, it will go to the last day of the previous month regardless of whether it is a different year or not.

    DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1


    Using this to generate the date for the MONTH or YEAR function will pull the month or year accordingly for the last day of the previous month no matter what the previous month is.

    MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

    YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

«1

Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    1-1 is not 12 is your issue.

    =COUNTIFS([140R]:[140R], 1, Date:Date, YEAR(@cell) = YEAR(TODAY()), Date:Date, MONTH(@cell) = month(date(year(2000,month(today()),1)-1)

    See if that works for you.

  • It didn't work. I received an "#INCORRECT ARGUMENT SET ERROR" after using that formula.

  • L_123
    L_123 ✭✭✭✭✭✭

    =COUNTIFS([140R]:[140R], 1, Date:Date, YEAR(@cell) = YEAR(TODAY()), Date:Date, MONTH(@cell) = month(date(2000,month(today()),1)-1)

    Give that a try. I made an error in the year part of the formula.

  • The formula works; however, I am receiving the result as 0 even though there's data there for the previous month.

  • I called Smartsheet support but they weren't able to figure out the formula as well. Scheduled a pro-desk session hoping they can assist with the formula. Thank you though for your help.

  • L_123
    L_123 ✭✭✭✭✭✭

    If you have blanks in any of the cells it could potentially pop an error. Give this a try:

    =COUNTIFS([140R]:[140R], 1, Date:Date, iferror(YEAR(@cell),0) = YEAR(TODAY()), Date:Date, iferror(MONTH(@cell),0) = month(date(2000,month(today()),1)-1)

  • Both the formulas work; however, they return a value of "0" for some reason. There's rows there for the previous month, but the formula isn't counting them for some reason.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

    The above will give you the last day of the previous month. If you pull the year and month from this date for the year and month for your calculations, you should be good to go.


    =COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

  • Hello Paul. That worked! Below is the formula I currently have which calculates the data for the current month. Do you think this should be revised as well?

    =COUNTIFS([140R]:[140R], 1, Date:Date, YEAR(@cell) = YEAR(TODAY()), Date:Date, MONTH(@cell) = MONTH(TODAY()))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I personally would use a slightly different approach, but that really is just personal preference. The only change I would actually suggest to your formula would be adding in the IFERROR portions in case there are issues in the Date column such as text entries or blanks. It isn't really NECESSARY, but it is an added level of security/accuracy.


    IFERROR(YEAR(@cell), 0)

    IFERROR(MONTH(@cell), 0)


    =COUNTIFS([140R]:[140R], 1, Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

  • Sounds good. 1 more question Paul. The formula you mentioned below for the Previous Month would work perfectly fine once February starts? It should look at January of 2020, correct?


    =COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Yes.


    DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

    will give you the first day of the current month.


    When you subtract 1 from that, it will go to the last day of the previous month regardless of whether it is a different year or not.

    DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1


    Using this to generate the date for the MONTH or YEAR function will pull the month or year accordingly for the last day of the previous month no matter what the previous month is.

    MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

    YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

  • Thank you @Paul Newcome and @L@123 for taking the time out and assisting me with this formula.

    Best Regards,

    Nikhil Chawla

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Nikhil Chawla Happy to help! 👍️


    Please don't forget to mark the most appropriate response(s) as "helpful" so that others looking for a similar solution can know that help may be found here.

  • @Paul Newcome Will the same formula not work if I try to get the counts for 2 months ago? I replaced "-1" with "-2" and that didn't work. The two months ago formula returns the same value as the Previous Month Formula.

    Previous Month Formula

    =COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))


    Two Months Ago Formula

    =COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 2), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 2)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!