count one column based on a different filtered column

hmcnamara95571

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

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

Paul Newcome

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

 

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

hmcnamara95571

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&lt;>"Complete")

Thank you!

Paul Newcome

What is the actual column name without the row reference?

 

Category

or

Category 18

 

hmcnamara95571

The column name is "Assigned To".

Paul Newcome

Then you will want to use

 

[Assigned to]@row

hmcnamara95571

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&lt;>"Complete")

 

Thank you

Paul Newcome

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

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!

Paul Newcome

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

Jim P

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

No column name...see attached...

hmcnamara95571

This gave me another "Unparseable" error.

Paul Newcome

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

 

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

hmcnamara95571

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