# Count Specific Instance Within Prior Month

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

Tags:

• ✭✭✭✭✭

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

• ✭✭✭✭✭

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

• Overachievers Alumni

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)

• ✭✭✭✭✭

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

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

• ✭✭✭✭✭

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

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

• ✭✭✭✭✭
edited 05/06/21

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!