COUNTIFS with 3 criteria

I was wondering someone could help me figure out what is wrong with my formula. I'm trying to reference another sheet and produce a countifs figure based on three different criteria:

Range 1: Type = Project

Range 2: Category = Digital

Range 3: RAG = Red

This is my formula:

=COUNTIFS({Children's Transformation Range 1}, "Project") + COUNTIFS({Children's Transformation Range 2}, (CONTAINS("Digital", @cell))) + COUNTIFS([{Children's Transformation Range 3}, "Red")))

The strange thing is it works fine with the first two criteria but is unparseable when I add the third.

Any help would be greatly appreciated!

Thanks

Louise

Find more posts tagged with

Accepted answers

All comments

Tim Shaded

At a quick glance it looked like there may have been some unnecessary brackets. It may work with below:

=COUNTIFS({Children's Transformation Range 1}, "Project") + COUNTIFS({Children's Transformation Range 2}, CONTAINS("Digital", @cell)) + COUNTIFS({Children's Transformation Range 3}, "Red")

Louise Chadderton

Thanks Tim, yes that works now.

Your help is much appreciated.

Thanks

Louise

Will Crawford

https://community.smartsheet.com/discussion/comment/302594#Comment_302594

Hi Tim,

Thanks for this the formula works.

Would you know we don't do it as a sum but with multiple criteria? It's just adding all the results together rather than picking out the specified containment it seems. Would using 'AND' be better?

Thanks

Will

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.