COUNTIF Query

David Slaughter

Hi All,

I'm trying to run a query to group and count certain status' in order to count them as a single statistic.

For example: If there's 4 'New / Reported' tickets and the 'Pending External Clariication' has 5 tickets, the OPEN tickets box would show 9 OPEN tickets.

Ticket classes:

Open ticket = The status' (New / Reported, Pending External Clariication, Pending Internal Clarification)

Closed tickets = The status' (Resolved / Released, Rejected)

I've managed to understand a lot of the other query structures but seem to be really struggling here, so was hoping someone would be able to help!

String I put together below:

=COUNTIFS({Change Request & Feedback Form Range 1}, [Status] : [Status], "New / Reported", "Pending External Clarification")

Thanks, David

Paul Newcome

It would go something like this...

=COUNTIFS({Change Request & Feedback Form Range 1}, OR(@cell = "New / Reported", @cell = "Pending External Clarification"), {Other Sheet Status Column}, @cell <> "Declined")

Paul Newcome

Sorry about that. One more thing I missed...

When you want to enter multiple range/criteria sets, you need to use a COUNTIFS with the S on the end.

=COUNTIFS({WF - Feeback Form / Change Request / Devel Range 1}, OR(@cell = "Pending External Clarification", @cell = "Pending Internal Review"), {WF - Feeback Form / Change Request / Devel Range 6}, @cell <> "Submitted")

Paul Newcome

Assuming {Change Request & Feedback Form Range 1} is the Status column on the other sheet, you would want something that looks like this...

=COUNTIFS({Change Request & Feedback Form Range 1}, OR(@cell = "New / Reported", @cell = "Pending External Clarification"))

David Slaughter

Excellent, thanks Paul! Really appreciate the help and the query string makes more sense now.

One more question if I may - How would I add in criteria to exclude status' that exist in another cell?

For example: I've now got the total number of grouped tickets (as described above), but what if I want to filter out any of those tickets that have a specific status in another cell.

Open tickets - (minus) any tickets from there that have the status 'Declined' in another cell?

Paul Newcome

It would go something like this...

=COUNTIFS({Change Request & Feedback Form Range 1}, OR(@cell = "New / Reported", @cell = "Pending External Clarification"), {Other Sheet Status Column}, @cell <> "Declined")

David Slaughter

Hey @Paul Newcome,

I tried the suggested query to exclude status' from the calculation in other columns and its returning 'UNPARSEABLE' (see string below). Any ideas why this is failing?

=COUNTIF({WF - Feeback Form / Change Request / Devel Range 1}, OR(@cell = "Pending External Clarification", @cell = "Pending Internal Review"), {WF - Feeback Form / Change Request / Devel Range 1} @cell <> "Submitted')

Feeling a bit dim at the moment! 🤦

Thanks David

Paul Newcome

There is a missing comma between your last range and criteria.

=COUNTIF({WF - Feeback Form / Change Request / Devel Range 1}, OR(@cell = "Pending External Clarification", @cell = "Pending Internal Review"), {WF - Feeback Form / Change Request / Devel Range 1}, @cell <> "Submitted')

David Slaughter

Thanks @Paul Newcome

I've added the comma but is still not pulling through:

=COUNTIF({WF - Feeback Form / Change Request / Devel Range 1}, OR(@cell = "Pending External Clarification", @cell = "Pending Internal Review"), {WF - Feeback Form / Change Request / Devel Range 6}, @cell <> "Submitted')

Any ideas?

David

Paul Newcome

I just noticed an apostrophe instead of a quote at the end. Here...

=COUNTIF({WF - Feeback Form / Change Request / Devel Range 1}, OR(@cell = "Pending External Clarification", @cell = "Pending Internal Review"), {WF - Feeback Form / Change Request / Devel Range 6}, @cell <> "Submitted")

David Slaughter

Thanks and sorry again. Ok, we're making progress but still not there! Response has changed now to #INCORRECT ARGUMENT SET (query below):

=COUNTIF({WF - Feeback Form / Change Request / Devel Range 1}, OR(@cell = "Pending External Clarification", @cell = "Pending Internal Review"), {WF - Feeback Form / Change Request / Devel Range 6}, @cell <> "Submitted")

Any ideas?

Paul Newcome

Sorry about that. One more thing I missed...

When you want to enter multiple range/criteria sets, you need to use a COUNTIFS with the S on the end.

=COUNTIFS({WF - Feeback Form / Change Request / Devel Range 1}, OR(@cell = "Pending External Clarification", @cell = "Pending Internal Review"), {WF - Feeback Form / Change Request / Devel Range 6}, @cell <> "Submitted")

David Slaughter

Thanks @Paul Newcome, really appreciate all your help.

Paul Newcome

Happy to help! 👍️