Count the number or non-blank cells in the specific ranges for 2 groups of rows

Xochitl C.

Hi, I'm trying to count 2 ranges of rows (in the RP Opportunities column) where cells are non-blank and also matches with the value of another column (this is a drop down list column with values "RP" and "Canada")

In this case I need to count only the non-blank cells if the value in the column named "queue (RE and Canada)" is "RP", however when trying I get the INCORRECT ARGUMENT error:

This is the formula I'm using for reference:

=COUNTIFS([COINCIDENCE AGENT/TICKET TYPE RP]@row:[Approved/Rejected URLs (RP)]@row, <>"", [QT-Sent & Language (RP)]@row:[Closing Ticket (canada)]@row, <>"", [Queue (RP and Canada)]@row, "RP")

Any idea is much appreciated!!!

Paul Newcome

If you just want to count how many rows there are that meet the criteria, I would suggest a helper column to mark each row that meets the criteria and then just sum this helper column.

=IF(AND([Queue (RP and Canada)]@row = "RP", COUNTIFS([COINCIDENCE AGENT/TICKET TYPE RP]@row:[Approved/Rejected URLs (RP)]@row, @cell <> "") + COUNTIFS([QT-Sent & Language (RP)]@row:[Closing Ticket (canada)]@row, @cell <> "") > 0), 1)

If you need to count the number of non-blanks:

=IF([Queue (RP and Canada)]@row = "RP", COUNTIFS([COINCIDENCE AGENT/TICKET TYPE RP]@row:[Approved/Rejected URLs (RP)]@row, @cell <> "") + COUNTIFS([QT-Sent & Language (RP)]@row:[Closing Ticket (canada)]@row, @cell <> ""))

Philip Robbins

Because you're working with multiple ranges you need to split your formula up.

=IF([Queue (RP and Canada)]@row= "RP",COUNTIF([COINCIDENCE AGENT/TICKET TYPE RP]@row:[Approved/Rejected URLs (RP)]@row, <>"")+COUNTIF([QT-Sent & Language (RP)]@row:[Closing Ticket (canada)]@row, <>""),"")

Paul Newcome

If you just want to count how many rows there are that meet the criteria, I would suggest a helper column to mark each row that meets the criteria and then just sum this helper column.

=IF(AND([Queue (RP and Canada)]@row = "RP", COUNTIFS([COINCIDENCE AGENT/TICKET TYPE RP]@row:[Approved/Rejected URLs (RP)]@row, @cell <> "") + COUNTIFS([QT-Sent & Language (RP)]@row:[Closing Ticket (canada)]@row, @cell <> "") > 0), 1)

If you need to count the number of non-blanks:

=IF([Queue (RP and Canada)]@row = "RP", COUNTIFS([COINCIDENCE AGENT/TICKET TYPE RP]@row:[Approved/Rejected URLs (RP)]@row, @cell <> "") + COUNTIFS([QT-Sent & Language (RP)]@row:[Closing Ticket (canada)]@row, @cell <> ""))

Xochitl C.

Thank so much @Philip Robbins and @Paul Newcome, both of the solutions worked perfect!

Great idea the helper column Paul, thanks so much!!

Paul Newcome

Happy to help. 👍️