COUNTIFS with 3 criteria

Louise Chadderton

Hi

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

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