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!
Answers
-
@mecliff if your are using that formula to look for 2022 data then it won't work to use year(today()) as that will return 2023.
Also you have to adjust for the month being 1 and then looking for 12.
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@Darren Mullen thanks for your response! If you have time, would you be able to offer guidance on what those year/month shifts would look like for this formula? Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!