Counting Harvey balls by row owner

GParkins

One column of my sheet is a 5-symbol Harvey ball (Sunny, Partly Sunny, Cloudy, Rainy, Stormy) to show the relative health of a row.

Another column is a Contact List type that shows to whom a row is assigned.

I would like to extract the total number of non-Sunny rows per team member.

What I have done is to add two columns.  The first is a list of each team member.  I'd like the second column to be the total number of non-Sunny rows that each team member has.

Here's the formula I used:

=IF([Assigned To]1:[Assigned To]409 = "John Smith", (COUNTIFS([At Risk]1:[At Risk]409, <>"", [At Risk]1:[At Risk]409, <>"Sunny")))

It returns an #INVALID OPERATION error. Does anybody have a suggestion on how I might extract the total number of non-Sunny Harvey balls by team member?

alex44

Smartsheet formulas sometimes do not work well with mixed types of data. So, I would create a second, hidden column on the main table that "converts" the symbol data to a text-only column - resulting in "Sunny" "Stormy" text in the cells.

Then do the calculation off of this new, hidden text-only column.

For example, if the symbol column is called "Symbol" and the first cell (row1) in that column is a sun image, then the hidden column to the right could be called "Symbol Text" and the formula for the first cell in that column =Symbol1 ... then formulas should work based on the Symbol Text column.