Formula to only count unique cells, no duplicates

Hello again,

is there an =countifs formula that can be used to only count unique cells which would avoid counting duplicate cells in that column?

below i want to count W01-W06 only once for the answer to be 6 instead of counting all the duplicate W01 to =8


thanks!

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @SRenner

    Are you looking for any other criteria, or just the number of unique values? To count the Unique Values in the Store Number column, you could use a COUNT(DISTINCT formula.

    Try this:

    =COUNT(DISTINCT([Store Number]:[Store Number]))


    If you were looking to add in more criteria (such as including a specific Date from that Date column), you could throw in the COLLECT function... for example:

    =COUNT(DISTINCT(COLLECT([Store Number]:[Store Number], Date:Date, DATE(2020, 04, 12)))


    Here are some Help Center articles I used: COUNT function / DISTINCT function / COLLECT function / DATE function / Referencing Columns

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @SRenner for your second question, could you copy/paste the formula you're using? Or provide a screen capture? I just tested up to 500 rows, with 260 unique values and it worked as expected.


    For looking for specific letters, my preference to build this would be to use the COLLECT and CONTAINS function, similar to how I referenced the date above.


    Try this:

    =COUNT(DISTINCT(COLLECT([Store Number]:[Store Number], [Store Number]:[Store Number], CONTAINS("R", @cell))))


    This would only work if your cells that begin with R are the only values that contain an R, as it doesn't look for where the R is located (does that make sense?)

    Glad you like the Community!! I love it, too. 🙂

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!