CountIf Formula with Multiple Criteria in the Same Column

Smartsheet Community:

I amnot sure what I am doing wrong but I am trying to Count the Number of Test Case if it Meets this Criteria:

John Doe

Test Case in Status of Pass, Fail, or Re-Test Needed

Here is my Current Formula that is Yielding 0 even though the Number should be 4:

=COUNTIFS({UAT Test Cases v2 Range 1}, "John Doe", {UAT Test Cases v2 Range 2}, "Pass", {UAT Test Cases v2 Range 2}, "Failed", {UAT Test Cases v2 Range 2}, "Retest Needed")

Find more posts tagged with

Accepted answers

Paul Newcome

Try this instead...

=COUNTIFS({UAT Test Cases v2 Range 1}, "John Doe", {UAT Test Cases v2 Range 2}, OR(@cell = "Pass", @cell = "Failed", @cell = "Retest Needed"))

All comments

Paul Newcome

Try this instead...

=COUNTIFS({UAT Test Cases v2 Range 1}, "John Doe", {UAT Test Cases v2 Range 2}, OR(@cell = "Pass", @cell = "Failed", @cell = "Retest Needed"))

Anthony Barthelemy

Excellent, Worked Perfectly!

Paul Newcome

Happy to help! 👍️

skanj670

I am trying to get a count by Lead Trainer but the formula doesn't include if there are multiple Lead Trainers in the same column.

Example, for Bryson Crittenden, it is counting 1 versus 3

=COUNTIF([Lead Trainer]:[Lead Trainer], "Bryson Crittenden")

Genevieve P.

Hi @skanj670

If you're looking in a multi-select column, you'll want to use the HAS function to see if the cell has that option along with others.

For example:

=COUNTIF([Lead Trainer]:[Lead Trainer], HAS(@cell, "Bryson Crittenden"))

See: https://help.smartsheet.com/function/has

Cheers,

Genevieve

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