COUNTIF Function Multiple Ranges

msmolloy ✭✭
edited 09/20/23 in Formulas and Functions

Hello all I am struggling with adding a second range with a Yes/No function. Below I have the basic function. What I am trying to do is get the count of cardiology that also meet the yes criteria.

=COUNTIF({02 Problem Metrics Range 1},"Cardiology")

Would I add an AND statement or what. This formula below does not work though.

=COUNTIF({02 Problem Metrics Range 1},"Cardiology") AND =COUNTIF({02 Problem Metrics Range 2},"YES")

Below this also did not work. The first one I copied off smart sheets training.

=COUNTIF({02 Problem Metrics Range 1},"Cardiology", [02 Problem Metrics Range 2, "Yes")


  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭

    Hi @msmolloy - You need to use the COUNTIFS formula. It would look something like this.

    =COUNTIFS({02 Problem Metrics Range 1}, "Cardiology", {02 Problem Metrics Range 2}, "YES")

    I hope that helps!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • msmolloy

    Thank you, Amber! One more question, would that COUNTIFS function return the number of cardiology departments that meet the yes criteria. I just want to clarify that is what is happening.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @msmolloy

    To clarify, a COUNTIFS with an S at the end allows you to add multiple filter statements into your COUNT function.

    The COUNTIFS will only count rows where all criteria are true.

    The way it works is that you list a column or range (e.g. your "Range 1") then a criteria (your "Cardiology"). Then you list another column or range ("Range 2") and your next criteria ("YES"). These have to be true in the same row.

    =COUNTIFS({02 Problem Metrics Range 1}, "Cardiology", {02 Problem Metrics Range 2}, "YES")

    In this formula, range 1 has to have Cardiology and in the same row, Range 2 has to say "YES".

    Here's more information:



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!