COUNTIFS with multiple designations

Jennifer Jack

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

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

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,

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

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