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
Comments
-
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.
-
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
-
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)
-
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.
-
Thanks Mike. This worked.
-
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!
-
Glad I was able to assist!
-
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.
-
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
-
Hi Craig,
nice addition of pulling lower(@cell) that would make it case insensitive. Nice addition.
-
Thanks Mike
Craig
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!