Countifs from one sheet with multiple categories

Options
Jasna
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?

Capture1.PNG com post .png COM POST 1 .png COM POST 2 .png COM POST 3.png


Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!