Countifs from one sheet with multiple categories

Jasna

I need to complete a countifs formula for data in one sheet with multiple categories (screenshots of data needed). The columns that need to be counted have different drop-down options.

I have to count active status defects the total (New, open, Assigned, Ready for Retest, Assigned to Vendor, Reopened) for each module. The modules are there are 7 modules, and they have to be devided by the testing cycle (SIT and Deffered to SIT) as well as counted by the Priority (1 - Critical, 2 - High, 3 - Medium, 4 - Low)

So far all of the formulas used give the same error #UNPARSEABLE

Here are the formulas I have tried:

=COUNTIFS({Module}, “Employee Central”, {Priority}, “1 - Critical”, {Testing Cycle}, “SIT”, {Status}, HAS(@cell, “New” + “Open” + “Assigned” + “Assigned to Vendor” + “Reopened”))

=COUNTIFS({Module}, “Employee Central”, {Priority}, “[1 – Critical]1”, {Testing Cycle}, HAS(@cell, “SIT”), {Status}, HAS(@cell, “New” + {Status}, HAS(@cell, “Open” + {Status}, + HAS(@cell, “Assigned”, + {Status}, HAS(@cell, “Assigned to Vendor”, + {Status}, HAS(@cell, “Reopened”))

=COUNTIFS({Module}, “Employee Central”, {Priority}, “1 - Critical”, {Testing Cycle}, HAS(@cell, “SIT”), {Status}, HAS(@cell, “New”), {Status}, HAS(@cell, “Open”), {Status}, HAS(@cell, “Assigned”), {Status}, HAS(@cell, “Assigned to Vendor”), {Status}, HAS(@cell, “Reopened”))

I even tried excluding the status options that do not need to be counted.

=COUNTIFS({Module}, “Employee Central”, {Priority}, “1 - Critical”, {Testing Cycle}, “SIT”, {Status}, <> “Fixed”, {Status}, <> “Closed”, {Status}, <> “Deferred”, {Status}, <> “Deferred Post Go-Live”, {Status}, <> “Cancelled”)

Is there another way to write the formula that may work?

Paul Newcome

Try retyping your last one in Smartsheet itself, here in the Community, or in a text editor (such as Notepad), but do NOT use programs such as Word.

Take a look at these quotes: " " " " " " " "

See how they are straight up and down whereas yours are slanted? Those slanted ones are called "smart quotes" which (ironically enough) are not recognized as valid characters in Smartsheet. Try replacing all of them with the ones that are straight up and down (using one of the methods above), and then we can take it form there if it is still not working.

Paul Newcome

Try retyping your last one in Smartsheet itself, here in the Community, or in a text editor (such as Notepad), but do NOT use programs such as Word.

Take a look at these quotes: " " " " " " " "

See how they are straight up and down whereas yours are slanted? Those slanted ones are called "smart quotes" which (ironically enough) are not recognized as valid characters in Smartsheet. Try replacing all of them with the ones that are straight up and down (using one of the methods above), and then we can take it form there if it is still not working.

Jasna

@Paul Newcome that fixed the issue. Thank you so much!

Paul Newcome

Happy to help. 👍️