Rankeq and Collect formula

cg80

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.

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)

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