CountIF where cell contains specific word

Phil Smith

Hi

 

I've read a few posts and dicussions but still can't seem to find an answer that I can use.

 

I'm collecting data and have some basic dashboards setup which display the number specific words are recorded.

 

As an example:

Clinical

Clinical Lead

Clinical Specialist Lead

Operational Lead

 

I need to be able to count how many 'Clinical' roles I have (the number returned should be 3). The key word is Clinical.

 

I'm looking for some sort of 'where' or 'contains' formula that I use with COUNTIF 

 

Kind regards

 

Phil

Christian Wells

Hi Phil,

Have you explored the COUNTIFS formula?

The formula will count one column based upon finding the criteria in another column.

Count a Number Column based on the column with the word "Clinical". 

 

Review the smartsheet Exmple Formula Template... there is a good explanation there.

 

I hope this helps

Bob Andrews

The best way to do this is to add an additional Text/Number column that checks the corresponding column for the word "Clinical" (you cant do this all from a single formula).

 

In the new column add this formula:

 

=FIND("Clinical", Roll1)

 

Then build a COUNTIF to count the number of values that are more than 0 in that column. 

 

=COUNTIF(Column:Column, ">" + 0)

Phil Smith

Thanks. Almost there, I can get the FIND and COUNT working ok against a single row but I can get it working down the complete column.

 

My column has muliple roles listed and I am just trying to do a count based on a ket word (for each role).

 

Bob Andrews

The FIND() formula will need to be added to each row. Once the formula is built and added to a row, you can copy it down by single left clicking in the cell containing the formula, hold shift and a single left click in the last cell in the column then press CTRL + D. This will fill the formula down the selected cells. Once the formula is added, use the COUNT function to get your results. 

 

Do you need to get a quick count or do you want to see the count results always displayed in your sheet? I ask, because you can have your FIND formula reference a cell in your sheet (rather than saying “clinical”). With this, you could put anything in that cell, and all your formulas would update to search for that term. Here’s an example:

 

https://app.smartsheet.com/b/publish?EQBCT=24f5f5ae0c3a43dca225e5c17e942917

 

The formulas in the FIND column are looking at the Count1 cell (yellow) and will ‘find’ it in the corresponding Role cell.

 

Count4 (Green) counts the number of rows the term appears in.

 

Count5 (Purple) counts and adds text (taken from Count1).

 

You can test it be changing the value in Count1. Try changing it to Lead or Operational. 

 

 

Joel Webley

This seems like it is way harder than it should be.  Is there no way for the developers to allow strings in the countif formula?

Bob Andrews

COUNTIFs can look for text strings but they just cant find specific strings inside of a string. It is all or nothing. 

aLgae

Hi, I am trying to create a summary table for the following scenarios:

a) Model is Available & CKP is Available 

b) Model is Discontinued but CKP is still Available

 

Q1)  Pls advise if the odd numbers (i.e. 22 and 13) are due to formula errors?  (I had thought that, when the cell contents is not my specified word, "0" will be displayed?)

 

Q2)  Pls advise easiest way to generate summary table for the above scenarios, thanks!  (i.e. how to make "sum"and "count" ignore inputs which have been filtered away from sight?)

 

Lindsay

I've found a way for the formula to look for a keyword in one column then sum the value in another column

=SUMIF(range, criteria, [sum_range])

Example:

=SUMIF([Work Type]1:[Work Type]148, "PM-weekly meeting", Value1:Value148)

 

Hope this helps

Troy S.

For this one, you need to use @cell



=COUNTIF(Range:Range, FIND("Search Value", @cell) > 0 



This functions as a COUNTIF Contains 

Beergod

Thanks Troy, this is exactly what I was looking for!