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

Tags:

Answers

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

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!