Multiple Counts of Project Type Column but Exclude if Complete from Status Column

I'm trying to count in my 'Type' column those labeled as "Business Planning", "HRO", "Operational Transformation", "Analytic Support" and "Strategic Implementation" while EXCLUDING those labeled as "Tasks" and then I want to EXCLUDE those 'Types' that are marked as 'Complete" or "Canceled' and only capture those labeled as "In Progress "NotStarted" "On going" or "On Hold"

I got this far in capturing types: =COUNTIF(Type:Type, HAS(@cell, "HRO")) + COUNTIF(Type:Type, HAS(@cell, "Business Planning")) + COUNTIF(Type:Type, HAS(@cell, "Operational Transformation")) + COUNTIF(Type:Type, HAS(@cell, "Analytic Support")) + COUNTIF(Type:Type, HAS(@cell, "Strategic Implementation"))

But I can't figure out how to now add in the piece of status and excluding those labeled "Complete' or 'Canceled' - Any help please??

Find more posts tagged with

Accepted answers

Mark Safran

Hey @Megan Hart

Give this a shot (where 'Status' is the name of your second column)

=COUNTIFS(Type:Type, <>"Tasks", Status:Status, <>"Complete", Status:Status, <>"Cancelled")

-MCS

All comments

Mark Safran

Hey @Megan Hart

Give this a shot (where 'Status' is the name of your second column)

=COUNTIFS(Type:Type, <>"Tasks", Status:Status, <>"Complete", Status:Status, <>"Cancelled")

-MCS

Kleerfyre

You need to look at a COUNTIFS formula. That would be where you need to look. You could also use the not equal to simplify your formula if you are only excluding one type. The piece for that part would look like this:

Type:Type, <>"Tasks"

Your COUNTIFS formula would look something like this:

=COUNTIFS(Type:Type, <>"Tasks", Status:Status, OR(@cell <> "Complete", @cell<> "Canceled"))

See if that works.

Megan Hart

https://community.smartsheet.com/discussion/comment/378558#Comment_378558

THIS WORKED!!! Thank you SO much for the help.

Megan Hart

https://community.smartsheet.com/discussion/comment/378558#Comment_378558

Got it working, thank you!!!

Sign in to join the conversation!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.