Countifs with and

HH_BE1894

I'm working on a metrics sheet and trying to count all the instances where a project is assigned to a particular person (metric@row) within the current year (parent cell) AND ONLY if the project status is New, In Progress or Approval Pending (Metric21, Metric22, and Metric23 in metrics sheet).

My formula before factoring in the project status:

=COUNTIFS({Broth Launch - Responsible}, FIND(Metric@row, @cell) >= 1, {Broth Launch - Year}, PARENT())

How do I edit this formula to return a sum of only the projects where {Broth Launch-Status} equals Metric21, Metric22 or Metric23?

Thanks!

Paul Newcome

Try this:

=COUNTIFS({Broth Launch - Responsible}, FIND(Metric@row, @cell) >= 1, {Broth Launch - Year}, PARENT(), {Status}, OR(@cell = Metric21, @cell = Metric22, @cell = Metric23))

Itai Perez

Hey @HH_BE1894,

Can you please add a screenshot for reference so I can understand more about what you need exactly?

I am thinking around IF(OR(Metric=Metric21,Metric=Metric22.....),count(collect))/Countifs... but seeing it will help me.

HH_BE1894

Sure, here you go! Val4 represents the project plan I'm tracking. I'm trying to populate how many tasks within that project are New, In Progress, or Approval Pending by person in the Task Assigned To column.

Paul Newcome

Try this:

=COUNTIFS({Broth Launch - Responsible}, FIND(Metric@row, @cell) >= 1, {Broth Launch - Year}, PARENT(), {Status}, OR(@cell = Metric21, @cell = Metric22, @cell = Metric23))

HH_BE1894

That worked - thanks!

Itai Perez

Hey,

Great thank you@Paul Newcome :)

Paul Newcome

Happy to help. 👍️