How to Rank item value ignoring empty or blank cells?

Elena Misa
Elena Misa ✭✭
edited 11/26/21 in Formulas and Functions

Hello. need help on this. I'm trying to rank the item name based on value % (highest to lowest). When one item name has no value % it gives a #NO MATCH on that item row and on the succeeding rows/item. Please see screenshot (sample).

Thank you. 😊

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭

    Hey @Elena Misa,

    There just needs to be an additional condition to the COUNTIF statement checking for a blank cell. Use try formula below.

    =IF([Value%]@row = "", "", RANKEQ([Value%]@row, [Value%]:[Value%]) + COUNTIFS([Value%]$1:[Value%]@row, [Value%]@row <> "", [Value%]$1:[Value%]@row, RANKEQ(@cell, [Value%]:[Value%]) = RANKEQ([Value%]@row, [Value%]:[Value%])) - 1)

  • Hi @Devin Lee,

    Thank you for your reply. Using your formula it gives an #INVALID DATA if cell has a value (value % column). Looking for formula that ranking of values will be continous even if there's a blank/empty cell(s).


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Elena Misa

    I've tested Devin's formula and it works fine on my sheet, however you could be getting an INVALID DATA TYPE error if somehow the % column is seen as text instead of numerical values.

    How did you enter in the Value %? Is it possible that these numbers are being read as text?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!