COUNTIFS with 3 criteria
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
Answers
-
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")
-
Thanks Tim, yes that works now.
Your help is much appreciated.
Thanks
Louise
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!