COUNTIFS with multiple designations

Good afternoon,

I'm trying to use a single column to determine all "Active" sites in a given county, which would include several designations in my "Tower Status" range, including "Pre-Planning", "Construction", etc. I have the following formula, to find the count of any one designation, but how would I add "Construction" and other designations?

=COUNTIFS({County}, County1, {Tower Status}, "Pre-Planning")

Find more posts tagged with

Accepted answers

Mark Cronk

Hi Jennifer,

What's not working? This formula should work for you. You'll enter the formula in your metric sheet. The formula refers to you external range {county} which needs to be your County column in the screenshot you sent and {tower status} which is the Tower Status column in the screenshot.

The formula needs to be in a row that has a column [County] because the formula is using that value to count results. You'll need to add your other "active" status categories to the formula. Based on the screenshot I added Sales.

=COUNTIFS({County}, County@row, {Tower Status}, OR(@cell= "Pre-Planning", @cell="Construction", @cell="Sales"))

Work for you?

Mark

All comments

Mark Cronk

Hi Jennifer,

Try:

=COUNTIFS({County}, County@row, {Tower Status}, OR(@cell= "Pre-Planning", @cell="Construction"))

You can add additional criteria to the OR. Insert a comma and the criteria.

Work?

Mark

Jennifer Jack

Thanks Mark! That didn't seem to capture it, unfortunately. In my spreadsheet, there are several sites in the same county, with different statuses. I've included a screenshot hoping this will help. Any other suggestions would be greatly appreciated!

Mark Cronk

Hi Jennifer,

=COUNTIFS({County}, County@row, {Tower Status}, OR(@cell= "Pre-Planning", @cell="Construction", @cell="Sales"))

Work for you?

Mark

Jennifer Jack

Thank you!!! This worked perfectly. User error. :)

Mark Cronk

Perfect. Glad you found a solution. Thank you for contributing to the Community.

Mark

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.