How do I use 'or' with CountIF?

I'm getting all twisted up in what I believe should be a Countif formula. I basically want to populate:

If the status is COMPLETE for Department A, B, or C I want that total.

If that status is IN PROGRESS for Department A, B, or C I want that total.

If that status is NOT STARTED for Department A, B, or C I want that total.

So the count total for example 1 should be 2.

Topic	Status	Dept
XX	Complete	Apple
XX	In Progress	Ball
XX	Not Started	Car
XX	Complete	Ball
XX	In Progress	Apple
XX	Not Started	Car

It is the 'or' that is tripping me up in creating this formula. Any suggestions?

Find more posts tagged with

Accepted answers

All comments

SoS | Dan Palenchar

Hello @Vivre Belle,

You can use COUNT(COLLECT()) for this. Try this formula and replace the various [VALUES] with what you specifically need.

COUNT(COLLECT(Status:Status, Status:Status, "[STATUS VALUE]", Dept:Dept, OR(@cell = "[DEPT VALUE A]", @cell = "[DEPT VALUE B]", @cell = "[DEPT VALUE C]")))

For example:

COUNT(COLLECT(Status:Status, Status:Status, "Complete", Dept:Dept, OR(@cell = "Apple", @cell = "Ball", @cell = "Car")))

Here is how it looks:

And here is a video showing the values changing and a published Sheet you can interact with.

Hope that helps!

Vivre Belle

Hi @Dan Palenchar

Thank you so much for this detailed explanation. If i have to insert a reference another sheet, do i place it in the beginning of this formula? When I did and i got the unparseable note. See example:

=COUNT({Reference, 3},COLLECT(Status:Status, Status:Status, "Complete", Dept:Dept, OR(@cell = "Apple", @cell = "Ball", @cell = "Car")))

Thank you!

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.