Countifs with and

Options
✭✭✭✭
edited 07/12/23

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!

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭✭✭✭✭
Options

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.

Itai Perez

Reporting and Project Manager

If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭✭✭
Options

That worked - thanks!

• ✭✭✭✭✭✭
edited 07/12/23
Options

Hey,

Great thank you@Paul Newcome :)

Itai Perez

Reporting and Project Manager

If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂