COUNTIF Function Multiple Ranges

msmolloy

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")

Amber Eakin

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!

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.

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:

https://help.smartsheet.com/function/countifs

Cheers,

Genevieve