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

COUNIF CONTAINS Question.xlsx

Thank you for your time in advance!

Regards

CCR

Find more posts tagged with

Countif

Accepted answers

KPH

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 multi-select dropdown list you can use HAS rather than CONTAINS.

KPH

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!

All comments

UserNewQuestions

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

KPH

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 multi-select dropdown list you can use HAS rather than CONTAINS.

UserNewQuestions

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

UserNewQuestions

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

Regards

CCR

UserNewQuestions

KPH;

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

Regards

CCR

KPH

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.