Using COUNTIF only if it qualifies...

L Petitt-RST

I am trying to create a status report for items started. I think I have the formula for how many assigned, but now I need to identify of those assigned to 1 person, how many are started by that person. I tried =COUNTIF({Course Started}, {Team Movement Range 1"Donald Duck"}1). In the pic below Donald has 2 courses assigned but has only started 1. If he had 20 courses assigned but has only started 7 - I want it to count the 7 checkboxes and return 7.

L Petitt-RST

I think I am actually trying to get this to work:

=Countifs({MODE Team AssignedDev}, [Assigned Developer]:[Assigned Developer], Donald Duck, [{MODE Course Started Col} [Course Started]:[Course Started],1])

I need to get the assigned developer and course started data from another page.

Mike Wilday

=Countifs({MODE Team AssignedDev}, "Donald Duck", {MODE Course Started Col},1)

If you use a cross sheet reference you just select the column you are referencing. And go with the name that is added to the formula. You don't have to add the column range afterward. Try what I revised there and let me know if that worked. Contact columns are weird because sometimes they pull by name, but sometimes they only pull by email address.

Mike Wilday

=Countifs would be your hero... slightly different but able to help you count with multiple criteria The following formula should do the trick for you.

=Countifs([Assigned Developer]:[Assigned Developer], Donald Duck, [Course Started]:[Course Started], 1

For more on COUNTIFS see:

https://help.smartsheet.com/function/countifs

L Petitt-RST

I have not forgotten you. :-) I am still trying to make it work as it still says unparseable. I think my issue is I am getting the data from another worksheet and need to account for that in the formula. Will update when I have made progress.

Mike Wilday

Sounds good. I'm here to help.

L Petitt-RST

I think I am actually trying to get this to work:

=Countifs({MODE Team AssignedDev}, [Assigned Developer]:[Assigned Developer], Donald Duck, [{MODE Course Started Col} [Course Started]:[Course Started],1])

I need to get the assigned developer and course started data from another page.

Mike Wilday

=Countifs({MODE Team AssignedDev}, "Donald Duck", {MODE Course Started Col},1)

If you use a cross sheet reference you just select the column you are referencing. And go with the name that is added to the formula. You don't have to add the column range afterward. Try what I revised there and let me know if that worked. Contact columns are weird because sometimes they pull by name, but sometimes they only pull by email address.

L Petitt-RST

BLESS YOU!!! You are a scholar and a gentleman. :-)

Mike Wilday

Awesome. If that worked please accept the answer! :)