count one column based on a different filtered column

hmcnamara95571 · 07/24/19

Hello,

I have a list of staff names in one column and another column with task statuses (Not Started, In Progress, Complete). I'd like to find how many In Progress and Not Started tasks each staff member has. I've tried COUNTIF and COUNTIFS but can't quite get the formula right. All of the examples I'm finding are looking for blank cells and none of mine are blank. I know this is incorrect but it's the closest I've come.....

=COUNTIF({Credentialing Tasks - Staff}, {Credentialing Tasks Status<>"Complete"})

Any ideas please?

Thank you!

hmcnamara95571 · 07/24/19

The formula listed above has the } in the wrong place but hopefully you understand what I'm trying to do.... Thanks again!

Paul Newcome · 07/24/19

You will need a COUNTIFS. Try something like this...

=COUNTIFS({Credentialing Tasks - Staff}, [Staff Name]@row, {Credentialing Tasks Status}, @cell <> "Complete")

hmcnamara95571 · 07/24/19

This (below) gave me an "Unparseable" error. The Category 18 is a staff name on my metrics sheet corresponding to a staff name on my tasks sheet. What am I doing wrong?

=COUNTIFS({Credentialing Tasks - Staff},[Category18]@row,{Credentialing Tasks Status},@cell<>"Complete")

Thank you!

Paul Newcome · 07/24/19

What is the actual column name without the row reference?

Category

or

Category 18

hmcnamara95571 · 07/24/19

The column name is "Assigned To".

Paul Newcome · 07/24/19

Then you will want to use

[Assigned to]@row

hmcnamara95571 · 07/24/19

This (below) gave me another Unparseable error. I'm sorry I just don't know what I'm doing wrong?

=COUNTIFS({Credentialing Tasks - Staff},[Assigned To]@row,{Credentialing Tasks Status},@cell<>"Complete")

Thank you

Paul Newcome · 07/24/19

Whwat are the steps you are taking to create your cross sheet references?

Are you able to post some screenshots with confidential/sensitive data removed, blacked out, or replaced with "Dummy Data"?

hmcnamara95571 · 07/24/19

I've attached two screenshots. One is the tracker where the data is being kept and added to. The other is the Metrics sheet where I'm trying to put the formula. Thanks for your help!

Metrics Example.PNG

Task Tracker Example.PNG

Paul Newcome · 07/24/19

What is the column name on your metrics sheet where your staff names are listed?

Jim P · 07/24/19

Try "=COUNTIFS({Credentialing Tasks - Staff},[Assigned To]@row,{Credentialing Tasks Status},"Complete")

Make sure the 2 sheet references are referencing the entire columns and not a specific cell. By doing an entire column, this will look for everything that matches your requirements.

hmcnamara95571 · 07/25/19

No column name...see attached...

Metrics Example 2.PNG

hmcnamara95571 · 07/25/19

This gave me another "Unparseable" error.

Paul Newcome · 07/25/19

"Category" is your column name. Try this...

=COUNTIFS({Credentialing Tasks - Staff}, Category@row, {Credentialing Tasks Status}, @cell <> "Complete")

hmcnamara95571 · 07/25/19

It works! Thank you so much for your help!!

count one column based on a different filtered column

Comments

Help Article Resources

Categories