How can I create a formula to count occurrences with THREE different value criteria.
I am trying to create a formula that will pull a count of occurrences with 3 specific values. I currently have a main sheet that is capturing a Child Care Center's student spaces by age and if the space is "Filled or Vacant." Screen capture of master sheet to source data for metrics sheet:
I then created a metrics sheet to track the cumulative data to later create a dashboard. The sheet is below:
What formula can I create to factor in the Center Name, Age Group, and Classroom Space for the metrics sheet?
Thank you for your help
Answers
-
@ASalinas Take a look at COUNTIFS() https://help.smartsheet.com/function/countifs
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Thank you for the response. I reviewed the "countifs" help page but still could not get a formula to work. I keep getting a "Unparseable" message. I tried several different formula's and still can not get it to work. This was the last formula I attempted to use:
=COUNTIFS({Early REACH Active Roster Range 1}, [Center Name]:[Center Name], "Precious Stones Academy", {Early REACH Active Roster Range 2}, [Age Group]:[Age Group], "Infant 0-17 Months", {Early REACH Active Roster Range 3}, [Classroom Space]:[Classroom Space], "Filled")
-
Hello @ASalinas,
It looks like you might be duplicating the range information. For a COUNTIFS formula, you will put in the range of what you want to count and then the criteria. So your formula should like something like this:
=COUNTIFS({Early REACH Active Roster Range 1}, "Precious Stones Academy", {Early REACH Active Roster Range 2}, "Infant 0-17 Months", {Early REACH Active Roster Range 3}, "Filled")
assuming that the Early REACH Active Roster sheet is your first screenshot above, Range 1 is the Center Name column, Range 2 is the Age Group column, and Range 3 is the Classroom Space column.
From there, you can adjust the criteria for each data field you're wanting to fill in your metrics sheet.
Monique Odom
Process Manager
Smartsheet Automagician
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
Help Article Resources
Categories
Check out the Formula Handbook template!