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



  • ASalinas

    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-Stearn
    Monique Odom-Stearn ✭✭✭✭✭✭

    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.

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom-Stearn

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    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!