Need to Count Projects by Assignee AND Status

Janelson0920

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.

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.

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!