COUNTIFS formula question for multiple choices

Hi there!

We're trying to count together the number of projects that have final invoicing complete, meet a PO # (on that row), in addition to three different project types (Standard 2-Page Case Study, Standard 1-Page Case Study, Blog Post with Customer Review).

We have the COUNTIFS formula to pull the references from multiple sheets for those that have final invoicing complete and match the PO #, but we're having issues adding in the different project types.

I know a option is to make three diffferent columns that would each pull one project type, and then have a total sum column, but is there a better way to write the syntax in one formula?

Find more posts tagged with

Accepted answers

KPH

The OR syntax is incorrect. You need parentheses for the OR function, which should be around the criteria and not the range. Like this:

=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], OR(@cell = "Text2", @cell = "Text3", @cell = "Text4"))

In your case the formula would look more like:

=COUNTIFS({Final Invoicing}, "Invoiced", {PO #}, [PO #]@row, {Project Type}, OR(@cell="Standard 2-Page Case Study", @cell="Standard 1-Page Case Study", @cell="Blog Post with Customer Review"))

All comments

KPH

Could you include a HAS function in your COUNTIFS?

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

Lydia Bowman

That presents us with an UNPARSEBALE issue. Right now, the syntax we have is:

=COUNTIFS({Final Invoicing}, "Invoiced", {PO #}, [PO #]@row, OR{Project Type}, "Standard 2-Page Case Study", "Standard 1-Page Case Study", "Blog Post with Customer Review") and getting UNPARSEABLE.

It works when you just have one condition, so:

=COUNTIFS({Final Invoicing}, "Invoiced", {PO #}, [PO #]@row, {Project Type}, "Standard 2-Page Case Study).

We suspect we've got the wrong syntax.

KPH

The OR syntax is incorrect. You need parentheses for the OR function, which should be around the criteria and not the range. Like this:

=COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], OR(@cell = "Text2", @cell = "Text3", @cell = "Text4"))

In your case the formula would look more like:

=COUNTIFS({Final Invoicing}, "Invoiced", {PO #}, [PO #]@row, {Project Type}, OR(@cell="Standard 2-Page Case Study", @cell="Standard 1-Page Case Study", @cell="Blog Post with Customer Review"))

Lydia Bowman

Thank you so much!

KPH

Happy to help!

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.