COUNTIFS/CONTAINS FUNCTION

CDS

I'm trying to count how many times the word promoter or detractor appears in a column but I keep getting 0

Paul Newcome

@CDS Try this:

=COUNTIFS([Thermostat CSAT Score]:[Thermostat CSAT Score], CONTAINS("Promoter", @cell))

Andrée Starå

Hi @CDS

I hope you're well and safe!

=COUNTIF([Thermostat CSAT Score]7:[Thermostat CSAT Score]140, CONTAINS("Promoter", @cell))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

CDS

it worked thank you Andree!

Andrée Starå

@CDS

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Houri

Hello Paul,

I use this formula: =COUNTIFS(Status:Status, "Active", [File Assigned to]:[File Assigned to], "Houri A")

However, it doesn't show the other assignments that my supervisor assigned me and other people to work on the same assignment. Should I use the CONTAINS formula or other ones? If CONTAINS is applicable, how should I apply this formula while using the COUNTIFS formula?

Paul Newcome

@Houri You would use it the same way it was used in the other solutions in this thread. You would wrap your criteria set in the CONTAINS function and use an @cell reference.

Houri

@Andrée Starå

I am still in need of help. Could you help me with this?

I use this formula: =COUNTIFS(Status:Status, "Active", [File Assigned to]:[File Assigned to], CONTAINS("Houri A", @cell))

The answer should be 5, however it is 0. It doesn't show the other assignments that my supervisor assigned me and other people to work on the same assignment. Should I use the CONTAINS formula or other ones? If CONTAINS is applicable, how should I apply this formula while using the COUNTIFS formula?

Genevieve P.

Hi @Houri

Is it possible that the [File Assigned to] column has your name spelled differently than what you have in the formula? Your structure is correct, however it looks like the CONTAINS is unable to find any cells that have "Houri A" in them.

Try just "Houri" to see if that brings back the right count:

=COUNTIFS(Status:Status, "Active", [File Assigned to]:[File Assigned to], CONTAINS("Houri", @cell))

If this hasn't helped, it would be useful to see a screen capture of your source sheet, but please block out sensitive data.

Cheers,

Genevieve

Paul Newcome

@Houri If your [File Assigned to] column is a contact type column, you will need to use HAS instead of CONTAINS.

https://help.smartsheet.com/function/has

Houri

@Genevieve P. and @Paul Newcome

Hello friends,

Thank you for your comments.

HAS worked for me. Also, I had to use HAS before using any other condition. Here is what worked for me:

=COUNTIFS([File Assigned to]:[File Assigned to], HAS(@cell, "Houri A"), Status:Status, "Active")

Paula_

@Paul Newcome

I have a variation for you that I am trying to solve. A sheet summary formula that is supposed to capture the count of lines that have $2,500 in fees listed (with these two options available to select, "$2,500." & "$2,500. + T&E") I am trying to get a total of both so I used just $2,500." in the formula - which is only counting the later of the two options instead of both.

Formula used: =COUNTIFS([Fee Structure]:[Fee Structure], CONTAINS("$2,500", @cell))

It should be 14, but is returning only 10.

Andrée Starå

Hi @Paula_

I hope you're well and safe!

It's probably because 10 is interpreted as text and the others as numbers.

Make sense?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paula_

Even though I am counting "$2,500" ?

Genevieve P.

Hi @Paula_

How are you entering the $2,500 into the cell when it's on its own? I agree that it sounds like the cell is seeing it as 2500 as a number and then formatting it. The input would need to be $2,500 with the comma and $ sign as a text value in the cell in order to be "found" by your formula

You can check this by doing a COUNT of the exact values:

 =COUNTIFS([Fee Structure]:[Fee Structure], "$2,500")

^ If this returns 4 then that's not the issue.

 =COUNTIFS([Fee Structure]:[Fee Structure], 2500)

^ If this returns 4 then that IS the issue, the values are seen as numerical. Does that help?