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.

 

Paul Newcome

You are getting an invalid operation error is because your first statement (the IF) is basically saying "IF the entire range = John Smith".

 

Try getting rid of the IF statement and using the John Smith reference in your COUNTIFS. And since you already have a column with the names listed, you don't need to enter specific text for a name and then change it for each different one. You can just use a cell reference. You don't need an additional "Symbols Text" column either. Give something like this a try...

 

=COUNTIFS([At Risk]1:[At Risk]409, AND(@cell <> "", @cell <> "Sunny"), [Contact Column]:[Contact Column], [Name Column]@row)

GParkins

@alex44 and @Paul Newcome, I got it sorted out. It was a combination of both of your ideas.  I created hidden columns for both the text of the Harvey ball and for the text of the "Assigned To" contact list column.  Next, I got rid of the IF statement and simplified everything else:

=COUNTIFS([Assigned to text]:[Assigned to text], "John Smith", [At risk text]:[At risk text], <>"Sunny")

Thank you both!

Paul Newcome

Happy to help. 

Mike Wilday

Hi GParkins, 

Glad you got it working! But I did want to note that symbols are easily referred to without helping columns. You just have to know the right "word" to use to reference the symbol accurately.

• https://www.smartsheet.com/blog/support-tip-column-symbols

Based on the above article, references to Harvey balls should use the following terms:

• Empty
• One Quarter
• Half
• Three Quarters
• Full

The helper column isn't necessary if you use these as your counters. So in the case of your COUNTIF, use the correct word from above to indicate sunny and your formulas won't need the helper Column.