Need to Count Projects by Assignee AND Status

I am having difficulty getting a project count by Person AND has to be one of 3 Statuses. Currently I have this formula to look at project count by a certain person but its pulling in all projects even DONE or Archived. NOTE: The assignee can be more than one person

=COUNTIF(GDC:GDC, FIND("first last", @cell) > 0)

All I want to do is add in another parameter to look at LIVE projects; so projects with a status of "In Progress" or "Meeting Required" OR "On Deck". I simply cannot figure it out.

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@Janelson0920

Switching your formula to COUNTIFS allows you to specify multiple criteria ranges, and the OR function lets you specify more than one possible criteria for a range. Try this:

=COUNTIFS(GDC:GDC, FIND("first last", @cell) > 0, Status:Status, OR(@cell = "In Progress", @cell = "Meeting Required", @cell = "On Deck"))

where Status is the name of your status column.

All comments

Jeff Reisman

@Janelson0920

Switching your formula to COUNTIFS allows you to specify multiple criteria ranges, and the OR function lets you specify more than one possible criteria for a range. Try this:

=COUNTIFS(GDC:GDC, FIND("first last", @cell) > 0, Status:Status, OR(@cell = "In Progress", @cell = "Meeting Required", @cell = "On Deck"))

where Status is the name of your status column.

Janelson0920

THANK YOU JEFF! I am new to SmartSheets and have been struggling with these formulas. I appreciate your help!

Janelson0920

@Jeff Reisman

The first one worked when looking at GDC column but when I swap in a different column (here its graphic designers) it says Unparseable. Any Insight?

Example:

=COUNTIFS([Graphic Designers]:[Graphic Designers], FIND("first last", @cell) > 0), Status:Status, OR(@cell = "In Progress", @cell = "Meeting Required", @cell = "On Deck"))

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318430#Comment_318430

Just a pesky extra closing parentheses right here:

=COUNTIFS([Graphic Designers]:[Graphic Designers], FIND("first last", @cell) > 0, Status:Status, OR(@cell = "In Progress", @cell = "Meeting Required", @cell = "On Deck"))

Janelson0920

@Jeff Reisman Thanks Jeff, appreciate you helping me out today!

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.