COUNTIFS - using multiple columns on CRITERION section
Hi all,
I'm having some trouble with a COUNTIFS formula, and I'm hoping you might be able to help me.
I am trying to highlight how many risks in a particular Risk Log have improved since mitigation plans have been put in place.
The aim is for the formula to count risks that are not Closed, and in which values in the RESIDUAL SCORE column are lower than their counterparts on the PRE-MITIGATION SCORE column.
The issue I'm having is I'm not sure how I'm meant to set the criteria for the second branch of the formula - highlighted below.
=COUNTIFS(Status1:Status18, OR(@cell = "Pending Action", @cell = "Being Mitigated"), [Residual Score]1:[Residual Score]18, @cell [Residual Score]1:[Residual Score]18 < @cell [Pre-Mitigation Score]1:[Pre-Mitigation Score]18)
I would be very grateful if you could give me any insights into what I'm doing wrong, or whether this is even possible. If it isn't I would also welcome any ideas on how to determine this =)
Should it be useful, I've included a link to the Sheet below:
Many thanks in advance, and I look forward to hearing from you!
Kind regards,
Gerhard
Best Answer
-
The easiest way to do this would actually be to add in a Helper Column which can indicate whether or not that one specific row meets your second half requirements.
For example, you could put this in a Checkbox column:
=IF([Residual Score]@row < [Pre-Mitigation Score]@row, 1, 0)
Then your COUNTIFS would count if that row has a checkbox or not:
=COUNTIFS(Status:Status, OR(@cell = "Pending Action", @cell = "Being Mitigated"), [Helper Column]:[Helper Column], 1)
Let me know if that will work for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
The easiest way to do this would actually be to add in a Helper Column which can indicate whether or not that one specific row meets your second half requirements.
For example, you could put this in a Checkbox column:
=IF([Residual Score]@row < [Pre-Mitigation Score]@row, 1, 0)
Then your COUNTIFS would count if that row has a checkbox or not:
=COUNTIFS(Status:Status, OR(@cell = "Pending Action", @cell = "Being Mitigated"), [Helper Column]:[Helper Column], 1)
Let me know if that will work for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P. ,
I had considered this but had hoped that there was a way of not resorting to helper columns.
Many thanks just the same =)
Kind regards,
Gerhard
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!