Count the number or non-blank cells in the specific ranges for 2 groups of rows
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!!!
Best Answer
-
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 <> ""))
Answers
-
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, <>""),"")
-
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 <> ""))
-
Thank so much @Philip Robbins and @Paul Newcome, both of the solutions worked perfect!
Great idea the helper column Paul, thanks so much!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!