Count Specific Instance Within Prior Month

JP Pedicino

Hello,

I am trying to get the number of instances on my 2021 Archive sheet where the "RFI Reason" was equal to "ADCVD". I've tried the following formula but it is giving me the count of all items from last month, not specifically those that have a RFI Reason of ADCVD from last month.

Any assistance would be greatly appreciated as the new guy tries to get the hang of the Smartsheet formula syntax.

=IF(MONTH(TODAY()) = 1, COUNTIFS({Created Date Range}, IFERROR(MONTH(@cell), 0) = 12, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Created Date Range}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY(), COUNTIF({2021 Archive RFI Reason}, "ADCVD"))))

Jen Lange

Hi @JP Pedicino. I think a COUNTIFS formula should suit you. It's hard to write formulas without the sheet, but let me know if this works for you:

=IF(MONTH(TODAY()) = 1, COUNTIFS({Created Date Range}, IFERROR(MONTH(@cell), 0) = 12, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Created Date Range}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {2021 Archive RFI Reason}, "ADCVD"))

-Jen

Jen Lange

Oh good! Okay, @JP Pedicino , try this...

=IF(MONTH(TODAY()) = 1, COUNTIFS({Created Date Range}, IFERROR(MONTH(@cell), 0) = 12, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Created Date Range}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {2021 Archive RFI Reason},CONTAINS("ADCVD",@cell))

-Jen

MCorbin

If I'm understanding correctly.....

You want to know the # of RFI Reasons from LAST MONTH that were = "ADCVD"

In that case, try this:

=COUNTIFS([RFI Reason]:[RFI Reason], "ADCVD", Date:Date, MONTH(@cell) = MONTH(TODAY()) - 1)

Jen Lange

Hi @JP Pedicino. I think a COUNTIFS formula should suit you. It's hard to write formulas without the sheet, but let me know if this works for you:

=IF(MONTH(TODAY()) = 1, COUNTIFS({Created Date Range}, IFERROR(MONTH(@cell), 0) = 12, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Created Date Range}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {2021 Archive RFI Reason}, "ADCVD"))

-Jen

JP Pedicino

Wow Jen, you did it, nice work!

After the fact I did realize that I need an adjustment. Rather than equal to "ADCVD" I need to say Contains "ADCVD. Can you assist?

Jen Lange

Oh good! Okay, @JP Pedicino , try this...

=IF(MONTH(TODAY()) = 1, COUNTIFS({Created Date Range}, IFERROR(MONTH(@cell), 0) = 12, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Created Date Range}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {2021 Archive RFI Reason},CONTAINS("ADCVD",@cell))

-Jen

JP Pedicino

Jen,

I was close just needed the @cell. Still trying to reprogram my Excel brain into Smartsheet. Thanks to people like you I look like a superstar, when the real star is you!

Thank you again so much!

Jen Lange

Don't worry, @JP Pedicino, I'm in the same boat of trying to train my Excel savvy brain to think in Smartsheet functions. It's a work in progress.

Also, thank you for your engagement and contribution to our community. Your inquiry is likely to assist other members across time AND it helps me flex my skills.

If you appreciate my response, please recognize the effort by with an "Insightful" or "Vote Up" selection. Thanks!

-Jen