Limit in Count Distinct Formula

radix
radix ✭✭✭
edited 11/20/20 in Formulas and Functions

I had this unpleasent experience using the following formula =COUNT(DISTINCT(data1:data1000))

It happens that the formula works only for the first 400 lines =COUNT(DISTINCT(data1:data400)) then the result will always be 1.

Is it a bug or am I doing something wrong?

· Share on FacebookShare on Twitter

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 11/21/20

    What kind of data is in that column? Also have you tried doing =Count(Distinct(data:data)) Which should grab the entire column?

    Also, have you tried seeing what =Distinct(data1:data1000) outputs?

    · Share on FacebookShare on Twitter
  • radix
    radix ✭✭✭

    "What kind of data is in that column? SANITARY CODES 12 digits Letters and numbers like"

    ADKELSHY598W

    "Also have you tried doing =Count(Distinct(data:data)) Which should grab the entire column?"

    Yes, same result.

    Also, have you tried seeing what =Distinct(data1:data1000) outputs?

    Yes all 1 if you select more than exactly 396 column if below the number is right

    use.png


    · Share on FacebookShare on Twitter
  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Did you make that a column formula? It looks like you're running that formula down the entire column. This formula should exist in a single cell... There is a strange mention of this issue on the notes section of the explanation of this formula. Do you have some values that all numbers in this list?

    I would also try selecting a smaller portion of the data and see if still shows 1. Something in that range is causing it to fail.

    https://help.smartsheet.com/function/distinct


    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!