COUNTIFS Formula

Cheryl Collins

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

Kelly Moore

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

Kelly Moore

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

Hi @Kelly Moore

Thats cracked it, thanks so much :-)