Countifs + find formula

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

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…