Need assistance with a formula

Nikhil Chawla

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)

Paul Newcome

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

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

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)

Paul Newcome

That's because the -1 portion is not subtracting a month. It is subtracting a single day from the first of the month which in turn gives you the last day of the previous month to pull the month number and year number from.

To go back 2 months, I would suggest changing -1 to -35. This takes the current date and goes back 35 days THEN references the month and year from that date.

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.

Nikhil Chawla

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

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.

Nikhil Chawla

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

Nikhil Chawla

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

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)

Nikhil Chawla

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

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

Nikhil Chawla

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

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

Nikhil Chawla

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

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)

Nikhil Chawla

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

Best Regards,

Nikhil Chawla

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.

Nikhil Chawla

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