Count the number of occurances of text strings

Astearn

I have 2 Smartsheets. Sheet 1 contains the data. Sheet 2 is a helper that I use to drive a dashboard. In sheet 1 I have course names. I don't want to have to explicitly code for each course as new courses may be added. In sheet 2, I want to return a value that is the course name in one column and in the next column return the count for that text string. In a perfect world, I would be able to create a formula that will put the top 5 courses in order from largest count to smallest. The range would be the same as the course title.

TIA for your help!

Kelly Moore

Hey @Astearn

The formula below should work for your Count column. Because you are creating a cross-sheet formula, you will need to physically create the references, you cannot simply copy paste the formula into your sheet.

=COUNTIFS({Sheet 1 Course Title column}, [Course Title]@row)

If you're not familiar with cross sheet references, this link might be helpful

There are a couple of options for the top 5 hits. You can create a report and sort descending on the Count column. It wouldn't just give you five, but it would be very quick to put together.

You could also create 5 sheet summary fields (found in right navigation bar) that would show your top 5 on the same sheet. If I wanted to be able to quickly see what were the top 5, this is the approach I would use on my sheets.

Here' is the formula for the #1 (highest count) Course Title

=INDEX([Course Title]:[Course Title], MATCH(LARGE([Count:Count, 1), Count:Count, 0))

The formula is the same for the other 4 fields, changing only the highlighted number. The numbers will be either 2 or 3 or 4 or 5, to give you the top 5 titles.

Will any of this work for you?

Kelly

Astearn

Thanks Kelly, it is returning unparsable. I clocked on "Reference Another Sheet" and it created the entire column as a range. I followed that  [Course Title]@row) as the criteria. Any ideas?

Kelly Moore

Hey @Astearn

Would you mind taking a screenshot of your actual formula? I want to see the colors

Note that an Unparseable is usually complaining about wrong column, misplaced commas, or misplaced/missing brackets

I did just notice I left an extra square bracket in the LARGE function for the #1 hit, if you are using any of those formulas. It should be

=INDEX([Course Title]:[Course Title], MATCH(LARGE(Count:Count, 1), Count:Count, 0))

Kelly

Astearn

Thanks!

- CT is the column reference in the other sheet. course_title is the column name

Kelly Moore

@Astearn

Your [course_title]@row is not colored. Delete the [course_title]@row from your formula and click your mouse into the [course_title]@row cell (the cell on the same row). This will make sure the name of the column is correct.

Astearn

I think part of the problem could be the data. In scrolling through it, I am seeing some non printable charachters. I am trying to add an IFERROR criterion but after an hour I am having no luck. You have no idea how much I appreciate your help!

Kelly Moore

@Astearn

I'm not sure errors in data would throw an unparseable. Did you first check the column name as I suggested? Your screenshot did not show me the column name. The column name should appear colored in your formula.

For your IFERROR

=COUNTIFS({CT}, IFERROR([course_title]@row,0))

Astearn

Kelly,

Thanks for your patience. I think we are missing the first step. Sheet 2 starts off blank. I want the formula to go in grab all of the unique names in Sheet 1's course title column and return those values into sheet 2. Then I want to count the # of times each name apears in sheet 1 and put the sum into the correct row in sheet 2. Does that make sense?

Kelly Moore

@Astearn

Aaaah. Ok that's not so straight forward to go into a different sheet that is blank. Somehow I missed sheet#2 was blank. If you're gathering into sheet #2 solely to see summaries, then I HIGHLY recommend simply creating a report. It's a one time deal, and will be completely evergreen for always. It will be completely hands free once you set up - which should take 10min or less.

The report will look very similar to your Sheet #2 original screenshot and can automatically be sorted into descending view.

Depending on your type of license plan, there are premier apps that can do this for you. And lastly, there is a manual way to do it but may require manual maintenance on your part, depending if you only want the top 6 or all of your titles

If you're unfamiliar with creating a smartsheet report, please see this link for creating Row Reports

Let me know what you decide and I'm happy to continue helping.

Kelly