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)?




Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!