Use of COUNTIF and CONTAINS to search 2 columns for a specific condition
Hello all!!!;
I am trying to search in rows across 2 columns for a specific condition that I want to count the total number of. These columns are in the same sheet (See attached).
My formula that returns "UNPARSEBLE" is:
=COUNTIF([AssignedText]:[AssignedText], CONTAINS("First.Last1", @cell, [NeedsText]:[NeedsText], CONTAINS("Quick Win, @cell))
I thinking that there may be an issue with one column containing multiple entries (Assigned) but not sure.
Any help with the above or another method to execute would be appreciated
.
Thank you for your time in advance!
Regards
CCR
Best Answers

Welcome CCR!
I can see three problems that will make the formula unparseable.
I will address them in order.
=COUNTIF
The COUNTIF allows for only 1 criteria. You can use COUNTIFS for multiple criterion.
This bit looks good
([AssignedText]:[AssignedText],
This is OK up to @cell,
CONTAINS("First.Last1", @cell
But then you need a ) to close the CONTAINS function.
Then this is OK up to "Quick Win
, [NeedsText]:[NeedsText], CONTAINS("Quick Win
Here you need a closing quotation mark
Then this looks good
, @cell))
The corrected formula is
=COUNTIFS(AssignedText:AssignedText, CONTAINS("First.Last1", @cell), NeedsText:NeedsText, CONTAINS("Quick Win", @cell))
You mention AssignedText containing multiple entries. If this formula does not return the correct result, and your column is a multiselect dropdown list you can use HAS rather than CONTAINS.

Hey that is wonderful. I was about to ask you to share a screen shot of your sheet so I could understand the problem. Glad I could help!
Answers

Correction: Note that the included formula is missing a " closer on the value Quick Win. Should read "Quick Win".
This is correct in the sheet formula. CCR

Welcome CCR!
I can see three problems that will make the formula unparseable.
I will address them in order.
=COUNTIF
The COUNTIF allows for only 1 criteria. You can use COUNTIFS for multiple criterion.
This bit looks good
([AssignedText]:[AssignedText],
This is OK up to @cell,
CONTAINS("First.Last1", @cell
But then you need a ) to close the CONTAINS function.
Then this is OK up to "Quick Win
, [NeedsText]:[NeedsText], CONTAINS("Quick Win
Here you need a closing quotation mark
Then this looks good
, @cell))
The corrected formula is
=COUNTIFS(AssignedText:AssignedText, CONTAINS("First.Last1", @cell), NeedsText:NeedsText, CONTAINS("Quick Win", @cell))
You mention AssignedText containing multiple entries. If this formula does not return the correct result, and your column is a multiselect dropdown list you can use HAS rather than CONTAINS.

KPH;
Thank you for your response and pointing out those issues. I have been trying multiple versions/permutations of the formula and issues with syntax are easy to overlook. Ok, so I took your recommendation and the formula did enter and work. The resultant though does not return the anticipated number or "3", it returns a "2". Taking your suggestion of using "HAS", if I replace the first "CONTAINS" with "HAS" the formula returns a "0". If I replace both "CONTAINS" with "HAS" the resultant is "0" (as expected since the first change produced the same result. Therefore I reverted the first "HAS" to "CONTAINS" and left the second "HAS" in place. This formula returned a "2" again (Below). "=COUNTIFS(AssignedText:AssignedText, CONTAINS("First.Last1", @cell), NeedsText:NeedsText, HAS("Quick Win", @cell))"
I am trying to keep these formulas compact and only use "Helping" columns when absolutely necessary. I have been through the formula sheets and have tried "AND" and other operators. Any suggestions on what steps to take next?
Thank you again for your help with this matter.
Regards
CCR

One additional detail that I forgot to mention, the formula is located in the "Sheet Summary" section of the sheet.
Regards
CCR

KPH;
OK I figured it out and the formula works perfectly, Thank you so Much!
Regards
CCR

Hey that is wonderful. I was about to ask you to share a screen shot of your sheet so I could understand the problem. Glad I could help!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 183 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!