formula to count multiple values in one column

Christiana Gkini

Hello,

I have a long column that contains over 1000 values and I want to count how many times the values appear. I tried to use the countif formula but it is not working as the values are sentences and there are over 85 different sentences, which makes it hard to capture each sentence in a formula. Could you please help me find the best way to capture this information?

Looking forward for your help!

Thank you!

David Tutwiler

Are you looking for a specific word to count or do you want to count how many times that exact sentence is used?

Christiana Gkini

Hi David,

I'm looking how many times every sentence appears in that column. To give you an example a small example of what the column looks like see the picture below.

David Tutwiler

Great, last question. Do you have a list of each sentence type, or does it need to be dynamic and do a count for each new type it might find?

Christiana Gkini

Hi David,

I do have a list of all the sentences but it would be nice to have the count in case there are new types added to the list.

Thank you

David Tutwiler

You could do this as another column on your sheet or on a separate metric sheet. I will post a screenshot and a formula below that should help. If you use it as another column, then every entry will show the total count of that sentence on the sheet. A summary column would show the count in the Summary field on the side of the sheet. The main difference is that this formula will be run for every row if you use it as another column. It will only be run once as a Summary field.

Column Formula

=COUNTIF([Type of Ticket]:[Type of Ticket], [Type of Ticket]@row)

*Look at the entire column for Type of Ticket and compare it against the sentence at the row you are evaluating

Column Screenshot

Summary Formula

=COUNTIF([Type of Ticket]:[Type of Ticket], [Type of Ticket]1)

Summary Screenshot

Paul Newcome

I would start with inserting a helper column on the sheet that houses the counts. This should be a text/number column and I will call it "Number" in this example.

In the Number column, you are going to need to manually enter the numbers 1 - whatever to provide enough rows to accommodate however many different strings you will have in the source data plus a bit of a buffer. If you think there will never be more than 85 unique strings in the column, then I would suggest maybe going on down to 110. You can enter 1 in the first row, 2 in the second row, highlight both cells and hover over the bottom right corner of the selection. The cursor should change to a "+". You can click and dragfill down to quickly populate the numbers in sequential order.

From there you would put this in your "List" column:

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet String Column}, {Source Sheet String Column}, @cell <> "")), Number@row), "")

Then you can get your counts by using this in a third column:

=IF(List@row <> "", COUNTIFS({Source Sheet String Column}, @cell = List@row)

Christiana Gkini

Hi David and Paul,

Thank you so much for your help.

I tried both ways and it didn't work for me. Please see the attached sceenshots and let me know what should I change?

Thank you!

David Tutwiler

In the first part (the COUNTIF formula) you just need to list the {Snow Q1 Range 1} once, not twice. If you are referencing another sheet the full reference comes over in the bracketed range.

In the second, the @cell mention isn't necessary and you don't have brackets around [Ticket Type]@row so it can't parse it.

Fixing one or all of those should help.

Christiana Gkini

Hi David,

Thank you for your instructions.

Here is what I fixed but it still doesn't give any results, am I missing something?

Paul Newcome

@Christiana Gkini A column name with a space, number, and/or special character needs to be wrapped in [square brackets].

=IF([Ticket Type]@row <> "", COUNTIFS({Source Sheet String Column}, @cell = [Ticket Type]@row)

@David Tutwiler The @cell reference may not necessarily be needed in this instance, but it never hurts (when used properly). I have run into instances where not using it actually caused issues (usually when a referenced column contains data with leading zeros), soo I have just gotten into the habit of always using it.

David Tutwiler

You have to reference any column with multiple words or spaces with brackets. It should be:

=COUNTIF({Snow Q1 Range 1}, [Ticket Type]@row)

Christiana Gkini

Hi Paul and David,

Thank you do much for your help.

I tried both but still looks like it isn't working. See the pictures for your reference. What am I doing wrong?

Paul Newcome

It looks like it is not registering "Ticket Type" as a valid column name. When typing out the formula, instead of typing the cell reference [Ticket Type]@row, try clicking on the cell instead.

Smartsheet "hides" extra spaces but stores them on the back end. You could have (for example) two spaces between Ticket and Type, it will only LOOK like one space, but will only work if you use two spaces.

Christiana Gkini

You were so right! It worked now!

Thank you so much Paul and David! Much appreciated!!

Paul Newcome

Happy to help. 👍️