COUNTIFS Formula
Hi
I am using a COUNTIF formula to count the number of projects, from my central intake sheet, that belong to a particular portfolio, have one of two status and belong to a particular programme e.g. Bus. Ops. Portfolio, Premises Programme with a submission status of Prioritised or Approved - Awaiting Prioritisation. The formula I'm currently using is:
=COUNTIFS({Projects Intake Sheet | Portfolio}, "Bus. Ops.", {Projects Intake l Submission Status}, "Prioritised", {Projects Intake l Submission Status}, "Approved - Awaiting Prioritisation", {Projects Intake l Programme}, Metric@row)
I am expecting a figure of 8 to be returned but I'm getting 0.
I can get the formula working correctly only using one project submission status but not one of both. I've tried using an OR but, it returned an error. I'm sure it's something super simple (it very often is)?
TIA
Cheryl
Best Answer
-
Hello @Cheryl Collins
As written, both of these status' would have to be present in the cell to return a count. It sounds as if you need an OR function instead of the written AND criteria.
=COUNTIFS({Projects Intake Sheet | Portfolio}, "Bus. Ops.", {Projects Intake l Submission Status}, OR(@cell= "Prioritised", @cell="Approved - Awaiting Prioritisation"), {Projects Intake l Programme}, Metric@row)
Would this work for you?
Kelly
Answers
-
Hello @Cheryl Collins
As written, both of these status' would have to be present in the cell to return a count. It sounds as if you need an OR function instead of the written AND criteria.
=COUNTIFS({Projects Intake Sheet | Portfolio}, "Bus. Ops.", {Projects Intake l Submission Status}, OR(@cell= "Prioritised", @cell="Approved - Awaiting Prioritisation"), {Projects Intake l Programme}, Metric@row)
Would this work for you?
Kelly
-
Hi @Kelly Moore
Thats cracked it, thanks so much :-)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!