COUNTIF Function Multiple Ranges

Options
msmolloy
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 ClinicalTrials.gov Problem Metrics Range 1},"Cardiology")


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

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

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

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

Answers

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Options

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

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

    I hope that helps!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • msmolloy
    Options

    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
    Options

    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 ClinicalTrials.gov Problem Metrics Range 1}, "Cardiology", {02 ClinicalTrials.gov 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:


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!