# Countifs + find formula

Options
edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭
Options

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)

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

Give that a shot.

• Options

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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.

• Options

Thanks Mike.  This worked.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Glad I was able to assist!

• ✭✭✭✭✭✭
edited 07/27/18
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Hi Craig,

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

• ✭✭✭✭✭✭
Options

Thanks Mike

Craig

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!