COUNTIFS Function | Smartsheet Learning Center
https://help.smartsheet.com/function/countifsCounts the number of times all given conditions in their respective ranges are met.
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")
Answers
-
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
-
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.
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.6K Get Help
- 435 Global Discussions
- 152 Industry Talk
- 495 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I am working in a separate sheet for calculating metrics. I want to count the number of items in a referenced date range in another sheet that meet certain year criteria. For example, I want to find t…
- I have a column that is a contact list where a single or multiple staff members can be selected to be scheduled. I'm trying to create a formula that counts how many times each individual's names are l…