Evaluate 4 columns with an OR then a fifth column with an AND
I have four status columns which have Red, Yellow, and Green options in my list of projects. I want to count how many projects (rows) have at least one Red. Then, I have a 5th column that if checked, I want to exclude from the count. I have this logic producing a row report for a dashboard. Now I want just the metric.
Any assistance would be greatly appreciated!
Answers

Hi @Scott at AC
The way I would do this is to add together 4 COUNTIFS Functions.
Ex:
=COUNTIFS() + COUNTIFS() + COUNTIFS() + COUNTIFS()
Each formula would count the number of rows that have Red in one of your Status columns and the "unchecked" value.
=COUNTIFS([Overall R/Y/G]:[Overall R/Y/G], "Red", Exclude:Exclude, 0) + COUNTIFS([Schedule R/Y/G]:[Schedule R/Y/G], "Red", Exclude:Exclude, 0) ... etc
You can put this formula in a Sheet Summary field, then reference it in a Metric Widget on your Dashboard.
Keep in mind this will create a double COUNT if any of your rows have a Red in more than one column, is this the correct Metric? If not, I would add a Helper Column in my sheet to check a box if your criteria is met Per Row, then Count by this helper column instead.
Ex:
=IF(AND(OR([Overall R/Y/G]@row = "Red", [Schedule R/Y/G]@row = "Red", [Budget R/Y/G]@row = "Red", [Scope R/Y/G]@row = "Red"), Exclude@row = 0), 1, 0)
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

You gave me a really good idea. The 'Helper Column' on the source sheet. If one or more columns (Overall, Scheduled, etc.) are Red, then the box is checked for that row. Then I simply need to count the Helper Column.
Thank you!
Scott
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!