COUNTIFS Formula

Options

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

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    Options

    Hi @Kelly Moore

    Thats cracked it, thanks so much :-)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!