Rankeq and Collect formula

I am having trouble with a Collect formula when I add another criteria. The Collect formula is embedded in a Rankeq formula. This formula (Rank column) works:

=IFERROR(RANKEQ(DateNum@row, COLLECT(DateNum:DateNum, DateNum:DateNum, @cell <> ""), 0), "")

When I add to the Collect portion of the formula (RankKL column) I get a blank.

=IFERROR(RANKEQ(DateNum@row, COLLECT(DateNum:DateNum, DateNum:DateNum, @cell <> "", Type:Type, "Key Leaders"), 0), "")

If I just use =COUNT(COLLECT(DateNum:DateNum, Type:Type, "Key Leaders")), I get a 1, whereas I thought it should be 3.

Any help would be appreciated.

Find more posts tagged with

collect

ranks

Accepted answers

Paul Newcome

What does this give you?

=COUNTIFS(Type:Type, "Key Leaders")

Paul Newcome

That's because there is a matching Datenum entry that has "Key Leaders".

Wrap the whole thing in an IF statement.

=IF(Type@row = "Key Leaders", RANKQ)

All comments

Paul Newcome

What does this give you?

=COUNTIFS(Type:Type, "Key Leaders")

cg80

The Type column has a vlookup formula, however, I didn't account for a "No Match" result in other rows. As soon as I added an IFERROR, to clear the error, everything calculated. I wouldn't have found it without your suggestion to try the COUNTIF formula. That resulted in a No Match which pointed me in the right direction. Thanks for your help.

cg80

The only strange thing is if Datenum is the same, it's ranked even if the Type is not Key Leaders.

=IFERROR(RANKEQ(DateNum@row, COLLECT(DateNum:DateNum, DateNum:DateNum, @cell <> "", Type:Type, "Key Leaders"), 0), "")

Paul Newcome

That's because there is a matching Datenum entry that has "Key Leaders".

Wrap the whole thing in an IF statement.

=IF(Type@row = "Key Leaders", RANKQ)

cg80

Thanks!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.