Need assistance with a formula

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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)

Answers

  • L@123[email protected] ✭✭✭✭✭

    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[email protected] ✭✭✭✭✭

    =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[email protected] ✭✭✭✭✭

    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.

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

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

  • Thank you @Paul Newcome and @[email protected] for taking the time out and assisting me with this formula.

    Best Regards,

    Nikhil Chawla

  • Paul NewcomePaul 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)))

  • Great @Paul Newcome ! That worked! Thank you so much once again!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

Sign In or Register to comment.