3 condition formula- Help

Need help with a formula, 3 conditions- 1) Recognize first 3 digits of "ticket number" (EX: "LEG", "ONB", "REV") 2) Recognize when a task is past due 3) Status not "Complete" or "Not Applicable"

What I have so far:

=COUNTIFS({KIM- Project Plan- TEST Range 3}, "LEG", {KIM- Project Plan- TEST Range 1}, >TODAY(), {KIM- Project Plan- TEST Range 2}, "In Progress", {KIM- Project Plan- TEST Range 2}, "Not Started")

Find more posts tagged with

Formulas

Support

Project Management

Accepted answers

Mark Cronk

Try:

=COUNTIFS({KIM- Project Plan- TEST Range 3}, left(@cell,3)="LEG", {KIM- Project Plan- TEST Range 1}, <TODAY(), {KIM- Project Plan- TEST Range 2}, AND(@cell <>"complete", @cell <> "not applicable"))

Mark

All comments

Mark Cronk

Try:

=COUNTIFS({KIM- Project Plan- TEST Range 3}, left(@cell,3)="LEG", {KIM- Project Plan- TEST Range 1}, <TODAY(), {KIM- Project Plan- TEST Range 2}, AND(@cell <>"complete", @cell <> "not applicable"))

Mark

Angelica Landers

@Mark Cronk It worked!! thank you so much, this was very helpful!

Mark Cronk

Hi Angelica, Glad you found a solution. Thank you for contributing to the community.

Mark

Angelica Landers

@Mark Cronk Mark, do you do Smartsheet Consulting work or are you just very helpful :) If you do, please connect with me angelica.landers@integritymarketing.com. I also have requested to connect with you on LinkedIn. Thanks in advance!

Mark Cronk

Hi Angelica, Smartsheets is just a hobby for me. No consulting. @Andrée Starå is the pro. I'm happy to connect with you and assist as my time allows.

Mark

Andrée Starå

@Mark Cronk

Thanks so much for the kind words! 😊

Angelica Landers

@Mark Thank you for the recommendation

@Andrea Vecchi What is a good email for you?

Andrée Starå

@Angelica Landers

Feel free to e-mail me at andree@workbold.com, and we will take it from there.

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.