Posts by schweggen

    For over 30'000 rows, the calculation engine will get stuck ...


    A shortcut would be to use VBA for the evaluation


    Code
    1. Sub TestUnique()
    2. MsgBox Evaluate("=SUMPRODUCT(((1/COUNTIF(A1:A34538,A1:A34538)*(B1:B34538=G2))))")
    3. End Sub

    That also works well thank you

    Try this, click the button on the sheet to display counts


    Code assigned to the button is

    I wanted to count one value but this would be fine, thank you

    If you highlight exactly this portion (B1:B10=G2)


    and Hit F9 to evaluate ...


    you should see : {FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}

    Then for my data set the return values are all false when selecting rows A1:A34538. The value I am looking for occurs 8 times within the column with duplicates. I replaced G2 with the value I am looking for using the same format.

    1. Have you adjusted the ranges to their actual sizes ...?


    2. Have you noticed in cell G2 ... the word Blue ... cannot be written Blue :

    1. I adjusted the ranges and I still receive 0, it only works on a small data set

    2. No I haven't, I'm not sure why it can't be written like that

    I have a column of names related by a column of numbers corresponding to each name. Each name is connected to a number and the same number can repeat. I want to count how many times a name occurs removing duplicates.