Requesting assistance with multipart COUNTIFS formula

Hi all,

My team and I currently use Smartsheet to track monthly referral information. At the beginning of each month, we are looking to obtain the number of referrals received for the prior month (ex: because it is January, we want to capture data from December). Here is what we are trying to track:

How many "SEN" referrals did we receive from "Intake/Ongoing" during the prior month.

This information is being pulled from 3 columns in one sheet (Referral Type, Referral Source, Referral Date). The following formula has been working for approx 6 months, but at the top of the new year it stopped yielding data. My guess is there's an issue with the YEAR formula, but I've been unable to resolve. Any help would be greatly appreciated!

=COUNTIFS({Referral Type Column}, CONTAINS("SEN", @cell), {Referral Date Column}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 12, 1)), {Referral Source Column}, CONTAINS("Intake/Ongoing", @cell))

Thank you!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!