CountIFS - Referencing the same Range

akelly

Hi - I am trying create a formula that counts if a reference spreadsheet range is assigned to development OR testing with the overall status of in progress given the priority.....I am newer and have been having a hard time transitioning my excel knowledge :( on doing ands and ors

=COUNTIFS({Sprint Spreadsheet Range 1}, 1, {Sprint Spreadsheet Range 2}, "Development", OR({Sprint Spreadsheet Range 2} = "Testing", {Sprint Spreadsheet Range 3}, "In Progress"))

Sprint Spreadsheet Range 1: Reference to identify all items that have priority of 1

Sprint Spreadsheet Range 2: Reference to task category "development" or "Testing"

Sprint Spreadsheet Range 3: Reference to task status of "in progress"

I would like to return the number of items that are a priority 1 in the category of development or testing with the status of in pogress

Mike Raposo

Hi,

This formula should work for what you're looking for. You want to be using the OR function for just the criterion aspect of the formula and not both the range and crtierion.

=COUNTIFS({Sprint Spreadsheet Range 1}, =1, {Sprint Spreadsheet Range 2}, OR(@cell = "Development", @cell = "Testing"), {Sprint Spreadsheet Range 3}, ="In Progress")

Hope this helps! Let me know if you have any questions.

Best,

Mike

Mike Raposo

Hi,

This formula should work for what you're looking for. You want to be using the OR function for just the criterion aspect of the formula and not both the range and crtierion.

=COUNTIFS({Sprint Spreadsheet Range 1}, =1, {Sprint Spreadsheet Range 2}, OR(@cell = "Development", @cell = "Testing"), {Sprint Spreadsheet Range 3}, ="In Progress")

Hope this helps! Let me know if you have any questions.

Best,

Mike

akelly

Exactly what I needed THANK YOU!