I need help with nesting syntax please

Lisa Kastelic

I am trying to count the number of tasks assigned to a resource, let's say "Jane". The COUNTIF function works if there is only one resource in the "Assigned To" category, but I also need to include the number of times Jane appears in the "Assigned To" category if there are multiple resources assigned to a task. So I think I need to nest the CONTAINS function within the COUNTIF function. The following syntax returns Incorrect Argument Set.

=COUNTIFS(CONTAINS("Jane", [Assigned To]:[Assigned To]))

What should the correct syntax be?

Paul Newcome

The CONTAINS function produces a true/false result. Because of this, we would use it as our criteria with an "@cell" reference to trigger the COUNTIFS on true values.

=COUNTIFS([Assigned To]:[Assigned To], CONTAINS("Jane", @cell))

Having said that... CONTAINS doesn't like Contact type columns. If your [Assigned To] is a contact type column, we can use a FIND function instead.

The FIND function generates a number that signifies where within a text string your specified criteria is found. So if you use FIND to search for "Jane" in a cell that reads "Jane Doe", the FIND function would produce a 1 because the first character in the string is where your specified criteria ("Jane") starts. Using the same thing on a cell that reads "John Doe, Jane Doe" would produce the number of 11 because the 11th character is where your criteria starts. If your criteria is not found within the cell, the FIND function will produce a 0 (zero).

So long story short, if all you want to do is determine if "Jane" is in a cell, then the FIND function can produce any number GREATER THAN 0 (zero).

FIND("Jane", @cell) > 0

If we drop that into your criteria for the COUNTIFS, it looks like this...

=COUNTIFS([Assigned To]:[Assigned To], FIND("Jane", @cell) > 0)

KEEP IN MIND: FIND is case sensitive, so if you are searching for "Jane", it will not locate "jane", etc...

Lisa Kastelic

This works perfectly! Thank you for the quick response and detailed explanation.

Paul Newcome

The CONTAINS function produces a true/false result. Because of this, we would use it as our criteria with an "@cell" reference to trigger the COUNTIFS on true values.

=COUNTIFS([Assigned To]:[Assigned To], CONTAINS("Jane", @cell))

Having said that... CONTAINS doesn't like Contact type columns. If your [Assigned To] is a contact type column, we can use a FIND function instead.

The FIND function generates a number that signifies where within a text string your specified criteria is found. So if you use FIND to search for "Jane" in a cell that reads "Jane Doe", the FIND function would produce a 1 because the first character in the string is where your specified criteria ("Jane") starts. Using the same thing on a cell that reads "John Doe, Jane Doe" would produce the number of 11 because the 11th character is where your criteria starts. If your criteria is not found within the cell, the FIND function will produce a 0 (zero).

So long story short, if all you want to do is determine if "Jane" is in a cell, then the FIND function can produce any number GREATER THAN 0 (zero).

FIND("Jane", @cell) > 0

If we drop that into your criteria for the COUNTIFS, it looks like this...

=COUNTIFS([Assigned To]:[Assigned To], FIND("Jane", @cell) > 0)

KEEP IN MIND: FIND is case sensitive, so if you are searching for "Jane", it will not locate "jane", etc...

Lisa Kastelic

This works perfectly! Thank you for the quick response and detailed explanation.

Paul Newcome

Happy to help! 👍️

Pierre Galiegue

Hi Lisa,

Let me suggest the following idea...

Add a check box column at the end of your sheet, called "is Jane involved on the task ?". Set the formula : =HAS([Assigned To]n;"Jane"), where n is the number of the line. This formula is required on all the lines.

Then, get the number of tasks assigned to Jane with the formula =COUNTIF([is Jane involved on the task ?];[is Jane involved on the task ?];1).

I suppose you need to get this information for each member of the team. In that case, based on this idea, you should set all the additional columns in a separate sheet.

I'm afraid this solution looks like heavy ... I did not get any better idea...

I hope it will help.

Pierre

Lisa Kastelic

Thank you, Pierre. Paul's solution seems a more efficient fit for this situation but I may have a different use for the one you suggested.