Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Count cells containing string

Sera T
Sera T ✭
edited 12/09/19 in Archived 2016 Posts

I'm trying to create a tally of the number of rows where a specific column contains a string. For example, the contents of a cell = "6-1, 6-2, 7-2" and I want this to be counted as 6-1, as 6-2, and also as 7-2. If this row were to only have one designation, so cell = "6-1", I could do this easily with COUNTIF. But although I can filter my rows by whether this cell contains "6-1", I can't figure out how to specify in a formula that it is allowed to contain "6-1" and not just equal "6-1". 

Also, I have a couple hundred of these designations, so I can't just have a checkbox column for 6-1, column for 6-2, and column for 7-2.

 

Any ideas? Thanks!!

· Share on FacebookShare on Twitter

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Sera,

     

    That's a touch one (for me). I'm having a difficult time picturing this.

    It sounds like a data entry nightmare, not only the back end of counting.

    Where will these formulas reside?

    If you are going to have a multiitude of "count if 6-1", "count if 6-2", etc... those are going to be in columns, aren't they?

    Are all of the designations in one column?

     

    A couple of things that might help without knowing more:

    1. when you filter a column, the system lets you know how many items are in the filtered list.

    2. a series of reports may be the way to go. each report for a specific designation. 

    this gives you the count as well.

    and if you filter more than a few times, will save you the time to do that because the report is just a click (or two) away.

     

    Hope this helps.

     

    Craig

     

     

     

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    edited 01/18/16

    Hi, are you looking to have a permanent formula for each item you are counting or could you use a dynamic formula that you can quickly update based on what you want to count?

     

    Check out this solution I came up with. I added a checkbox column with a formula that will check the box if the corresponding cell contains the value you specific in a fixed cell. Then there is a formula that counts the checkboxes. 

     

    The brown cell contains the value the checkbox formulas are looking for. When you change this value, all the checkbox formulas update to search for the new value. This can get you a quick count for a specific value.

     

    The green box contains the count formula and purple box contains the same formula but with concatenated text which is taken from the value in the brown box. 

     

    Here's the sheet:

     

    https://app.smartsheet.com/b/publish?EQBCT=4da45e3ac9e04277bdb86844283468b1

     

    Test it out by changing the value in the brown box. 

    · Share on FacebookShare on Twitter
  • Sera T
    Sera T ✭
    edited 01/19/16

    Thanks so much for the suggestions! These are both useful, but unfortunately I do think we need to be able to have a list of all the counts around all the time – the purpose is to be able to assess the coverage of each designation by the whole dataset. If there's a list we can scan, it's a 10-minute job to find the areas of low coverage, but if we have to manually move through reports or change a formula it becomes a several-hours job.

     

    I was wishing I could get a COUNTIF formula to iterate through the cells of a column and perform the FIND formula on each of them. I think my tentative plan is to make another sheet, link to the relevant column containing all the designations, and go through and make a FIND column like Travis did above for each designation. I'll put a count at the top of each column, and then this will be either useable as-is, or I can link from somewhere else to the counts. Kind of a round-about way of getting something to iterate, but I think it should work.

     

    My other possible non-elegant solution is to provide 5 or 6 columns to add up to one designation per cell instead of a concatenated "6-1, 6-2, 6-3". Then I'm thinking I could get a nested COUNTIF to work.

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    edited 01/19/16

    Will each cell contains three values? "6-1, 6-2, 6-3"

     

    You might consider changing the way you enter the information. You could have three columns, one for each value, then have a forth which can concatenate the values from the other three columns. With this method you could use a COUNTIF to count the number of times a value exists in the three columns.

     

    Here's an example:

     

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

     

    Data in columns Value 1, Value 2, and Value 3 are added manually.

     

    This is the formula in the Concatenate column:

     

    =[Value 1]1 + ", " + [Value 2]1 + ", " + [Value 3]1

     

    This is the formula in the Primary column. I added text at the end, but this can be removed if you dont need it:

     

    =COUNTIF([Value 1]1:[Value 3]8, "6-1") + " are 6-1"

     

    This is set up for three values but you can add as many columns as you need. Just adjust the formulas to account for the additional columns. 

    · Share on FacebookShare on Twitter
  • Sera T
    Sera T ✭

    Thanks! I think I'll do it this way. Most rows will have only one value, with some rows having (hopefully not more than) 5 values, so it's not the most elegant use of space, but it will certainly get the job done!

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories