How do I count the number of stars when using the symbols columns?

Keri OShea

How do I count the total number of stars when using the symbols columns? I have tried the count formula and it is only counting the number of cells and not the total number of stars, I have tried the sum formula and it is returning 0.

Mike Wilday

Hi Keri, I have tried several methods to get the star values of one, two, three, four, five to convert to a number format but I have been unable to get that number to convert. I would recommend reaching out to support and asking if they have a workaround.

@Andrée Starå @Paul Newcome @L@123 Do any of you guys know how we can get the stars format to register as a number? I tried converting the value returned to a text cell to text, and then converting that to a value to count, but I was unsuccessful.

Any thoughts?

Keri OShea

Hello Mike,

Thank you for the quick response. Hopefully the other users you've mentioned can provide a solution.

Mike Wilday

@Genevieve Penny

Is this a bug in the system? I tried to convert a stars field to a value but kept getting an erorr?

I created a star column called stars and tried using =value(stars@row) to convert the star but I kept getting errors.

A Rose

Hi @Keri O'Shea

Let's say you have those symbols in a column called: "How satisfied are you with our service?"

I recommend adding a column, and adding a formula as below

=COUNTIF([How satisfied are you with our service?]:[How satisfied are you with our service?], "Five")

And Change to "One" or "Two" etc.

Pls let me know if that worked.

Thank you!

Paul Newcome

The only way I have found to do this is through a "brute force" formula approach. To count across multiple columns, I personally would use...

=COUNTIFS([First Column]@row:[Last Column]@row, "One") + (COUNTIFS([First Column]@row:[Last Column]@row, "Two") * 2) + (COUNTIFS([First Column]@row:[Last Column]@row, "Three") * 3) + (COUNTIFS([First Column]@row:[Last Column]@row, "Four") * 4) + (COUNTIFS([First Column]@row:[Last Column]@row, "Five") * 5)

Basically you count how many are 5 stars and multiply by five then count how many are 4 stars and multiply that by then the 3 stars by three and the two stars by two then add all of those together along with the count of one star and that will tell you how many stars total you have in that row across those columns.

Paul Newcome

The reason a direct conversion doesn't work is because the value on the back-end is a text value such as "One", "Two", "Three", etc. It isn't stored as 1, 2, 3, etc.

Joseph Gentile

Another option, if you're goal is to get a total count of all the stars added together...

My example shows 5 columns with a "Total" column that adds up all "Stars." I had to use a nested IF formula to convert the 'words' into 'numbers', i.e One=1, Two=2,...Five=5.

In the last column uses the following formula. You'll have to adjust the formula to add more SUM statements for each of your columns. IT's a bit lengthy, and maybe there's a way to simply the formula.

=SUM(IF([Column 1]@row = "One", 1, IF([Column 1]@row = "Two", 2, IF([Column 1]@row = "Three", 3, IF([Column 1]@row = "Four", 4, 5))))) +

SUM(IF([Column 2]@row = "One", 1, IF([Column 2]@row = "Two", 2, IF([Column 2]@row = "Three", 3, IF([Column 2]@row = "Four", 4, 5))))) +

SUM(IF([Column 3]@row = "One", 1, IF([Column 3]@row = "Two", 2, IF([Column 3]@row = "Three", 3, IF([Column 3]@row = "Four", 4, 5))))) +

SUM(IF([Column 4]@row = "One", 1, IF([Column 4]@row = "Two", 2, IF([Column 4]@row = "Three", 3, IF([Column 4]@row = "Four", 4, 5))))) +

SUM(IF([Column 5]@row = "One", 1, IF([Column 5]@row = "Two", 2, IF([Column 5]@row = "Three", 3, IF([Column 5]@row = "Four", 4, 5)))))

Mike Wilday

https://community.smartsheet.com/discussion/comment/270909#Comment_270909

Duh. 😊 I should have figured that. Thanks. It's been a LONG weekend.

L_123

Yeah I agree. Brute force is the way to do this regretfully. I attempted to solve this in several ways and was unable to come up with an elegant solution. Best answer I have right now is posted at the bottom.

A few of the ways I thought of were:

Text length. Using LEN to convert to number, then converting to the correct number based off text parsing. Waaay to long.

Stacked substitutes. would work, but not faster or better than sumifs

Parsing via unique letters: Would work, as each word has at least one unique character from the rest. but again, no real benefit as the conversion formula from text to numbers is longer and slower than static text references.

Replace Contains

=Replace(Item@row,1,6,if(contains("i",item@row),5,if(contains("n",item@row),1,if(contains("h",item@row),3,if(contains("w",item@row),2,if(contains("u",item@row),4),0

This is a fun one that I really like conceptually, but it relies on a helper column for each cell containing the stars. At this point I realized I was just having fun making formulas, and gave up trying to make the most efficient formula.

Below is what I would go with, where your leftmost star column is named A, your rightmost star column is named E, and all star columns are consecutive with nothing else between them.

=countif(A@row:E@row,"one")+(countif(A@row:E@row,"two")*2)+(countif(A@row:E@row,"three")*3)+(countif(A@row:E@row,"four")*4)+(countif(A@row:E@row,"five")*5)

Paul Newcome

@L@123

"At this point I realized I was just having fun making formulas......."

I can't lie. I have done this on multiple occasions too. I have been known to build in jokes and games at times if you knew where to start looking. Haha. I might have to start doing that in some of my solutions that I have published to the community.

Bassam Khalil

Nice @Paul Newcome 😂😂

L_123

@Paul Newcome Lol I need to read the comments through before posting, we have almost the exact same solution. And that sounds like fun, i'll toss in some fun stuff on the next few things I publish here.

Paul Newcome

@L@123 Haha. No worries.

But yeah. I have set it up so that this one random seemingly empty cell is just formatted so that the font and background match, but when you hover over it, you are directed to another seemingly empty cell which has you check a specific box then enter specific text in another cell and so on and so forth and next thing you know there is a giant "CONGRATULATIONS" in the middle of the screen.

Or if there is something people keep messing up, I will replace the error with "You weren't supposed to do that".

Or even just a random "Hi" or something. Haha.

I'm currently working on a hidden tic-tac-toe game that I can put into a sheet somewhere. Hahaha.

Genevieve P.

https://community.smartsheet.com/discussion/comment/270904#Comment_270904

@Mike Wilday sorry for the delay, but it looks like you all have figured out some great alternatives using a "brute force formula"!

@Paul Newcome & @L@123 looking forward to seeing some of those games 😂

Back in April I created a Battleship game across 2 sheets with cell-linking to show the other player when a ship is Sunk. Then I built in a "WNNER!!" message using formulas once all of the other user's ships were sunk. Had a bit too much fun.

Paul Newcome

@Genevieve P HAHAHAHA!! That's great! I've only ever done "single player" kinds of things isolated on a single sheet. I've never done a "multi-player" kind of thing across multiple sheets. Now I'm going to have to play. Hahaha.