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!

Find more posts tagged with

Sheets

Formulas

Support

Accepted answers

All comments

Darren Mullen

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

mecliff

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.