Countifs Error for 3 references
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 (1Critical, 2 High, 3Medium, 4Low)
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}, [1Critical]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?
Answers

@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}, [1Critical]1)
The above formula would work on every row in the [1Critical] 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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 201 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!