Countifs + find formula

Steve Fullmer

I have a formula I'm having an issue with.   I'm trying to count how many projects are assigned to a individual in the "Assigned To" column that contains a partial description "green Ties" in the "task Name" column.

here's my formula I came up with and need help to get it right.

=COUNTIFS([Assigned To]1:[Assigned To]160, =Dan R,[Task Name]1:[Task Name]160, FIND("green Ties" @cell) > 1)

Thanks

Paul Newcome

You are going to need a hidden helper checkbox column for this. In that column you will enter

=IF(FIND("green Ties", [Task Name]@row) > 0, 1, 0)

Then change your COUNTIFS to

=COUNTIFS([Assigned To]1:[Assigned To]160, "Dan R" ,[Helper Column]1:[Helper Column]160, = 1)

 

Give that a shot.

 

Steve Fullmer

Hi Paul,

 

Can you have multiple helper columns for additional names?  Can you help explain what the = 1 at the end of the formula does?

 

Thanks

Mike Wilday

I think you might be able to accomplish this without a helper column. It looks like your Find formula is missing a comma... 

Try this one. 

=COUNTIFS([Assigned To]1:[Assigned To]160, =Dan R,[Task Name]1:[Task Name]160, FIND("green Ties", @cell) > 1)

Paul Newcome

Good catch. I missed that part. The only thing I would recommend changing is the >1 to >0. If it is >1 it will only count if "green Ties" appears at least twice within the same cell. >0 means it only has to be in there once for it to count.

Steve Fullmer

Thanks Mike.  This worked.

Mike Wilday

Not quite accurate, but you're recommendation still makes sense. Find isn't returning the number of times it appears. FIND is returning the position where the Gren Ties appears in the string. If green ties is at the beginning of the cell it would return the position 1 and WOULD not count it based on my where Green Ties is > 1. Using > 0 will ensure that any appearance, including at the first position would be counted. Thanks for pointing that out! 

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

Mike Wilday

Glad I was able to assist!  

Paul Newcome

Sad part is... I knew that... I use it in formulas for that reason on a regular basis. -_-

It has been one looooong week. I think a 6 month vacation should just about recharge my batteries.

 

Thanks Mike.

J. Craig Williams

Both of the "last" answers I see have problems, addressed in comments but not in an example

=COUNTIFS([Assigned To]1:[Assigned To]160, "Dan R", [Task Name]1:[Task Name]160, FIND("green Ties", @cell) > 0)

changes Paul's version to no extra column and fixes the name to "Dan R".

To add something to the conversation:

If you want to search for "Green Ties" or "GREEN TIES" or "green Ties", then this will find any version (case insensitive)

=COUNTIFS([Assigned To]1:[Assigned To]160, "Dan R", [Task Name]1:[Task Name]160, FIND("green ties", LOWER(@cell)) > 0)

Craig

Mike Wilday

Hi Craig, 

nice addition of pulling lower(@cell) that would make it case insensitive.  Nice addition.

J. Craig Williams

Thanks Mike

Craig