Countifs Error for 3 references

Jasna

I am trying to get a metrics report for a dashboard for a log with multiple test cycles and multiple data.

My metrics are by Status (New, Open, Cancelled, Assigned to Vendor, etc)

• Each of the Status options has to be filtered by the testing cycle which is either SIT or Deferred to SIT (I know I will have to do the same formula but change the testing cycle)
• It also has to be filtered by the priority (1-Critical, 2- High, 3-Medium, 4-Low)

I have spent hours trying to find the solution and below is the formula that keeps being mentioned.

=COUNTIFS({Status}, "New", {Testing Cycle}, "Deferred to SIT", {Priority}, [1-Critical]1)

I reference the sheet where all of the data is stored with the correct column and each time I either get a count of 0 or an error message.

What is the correct formula in this situation?

Jeff Reisman

@Jasna Without seeing the data columns you are referencing it's hard to tell what's wrong. Also knowing what error message you get when it errors would help too.

The troubleshooting I would recommend would be to see which of your criteria are working and which are not; to do this, start with one criteria and see if you get better results:

=COUNTIFS({Status}, "New")

If that does count everything with status "New", then add in your next criteria, and so on.

Check that the ranges you reference are the same size; like if {Status} references the entire Status column in your data sheet, be sure that {Testing Cycle} and {Priority} reference their entire columns as well, or alternatively, if only considering a subset of rows, make sure they all reference the same subset of rows.

One other thing, just to make it easier to use the same formula for each row in your metric sheet - instead of the criteria {Status}, "New", use @row functionality:

=COUNTIFS({Status}, [Primary Column]@row, {Testing Cycle}, PARENT([Primary Column]@row), {Priority}, [1-Critical]1)

The above formula would work on every row in the [1-Critical] column without needing to be changed for each status value or testing cycle. And to use it in your other metrics columns, all you'd need to change is the last criteria.