Question

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:

https://app.smartsheet.com/b/publish?EQBCT=1d166ed9a22d476c874ae7c344862390

Many thanks in advance, and I look forward to hearing from you!

Kind regards,

Gerhard

Genevieve P. · Accepted Answer

Hi @Gerhard da Costa Pinto

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