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
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!