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

  • Genevieve P.
    Genevieve P. Employee Admin
    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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!