Limit in Count Distinct Formula

radixradix
edited 11/20/20 in Formulas and Functions
11/20/20 Edited 11/20/20
Answered - Pending Review

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?

Answers

  • Mike WildayMike 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?

  • "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


  • Mike WildayMike 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.


Sign In or Register to comment.