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

Find more posts tagged with

Formulas

Accepted answers

All comments

Darren Mullen

@ASalinas Take a look at COUNTIFS() https://help.smartsheet.com/function/countifs

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.