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!!!

Find more posts tagged with

Formulas

Countif

Accepted answers

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 <> ""))

All comments

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 you need to count the number of non-blanks:

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.