none
Counting unique record up RRS feed

  • Question

  • I have a table with 6 columns and I want to add a formula in H2 to count the number of uniques records in column $D$1: D2 (D3, D4, D5, ..... something like 

    D1 - Pen                             count 1

    D2 - Pencil                          count 2

    D3 - Paper                          count 3

    D4 - Pen                             count 3

    D5 - Light                            count 4

     

    Fernando Salgueiro

    Tuesday, August 14, 2018 9:47 PM

All replies

  • Hi,

    Do you provide the incorrect sample about your problem?

    Do you want to get the result as shown in the following picture?

    H1: =COUNTIF($D$1:D1,D1)

    If my understanding is incorrect, plesae provide a dietail table file or screenshot about your problem.

    I'm glad to help you.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, August 15, 2018 12:41 AM
    Moderator
  • Excel 2016 Pro Plus with Power Query (aka Get & Transform)
    Count distinct items in record sequence.
    http://www.mediafire.com/file/mu61c1sfyrgmgz5/08_14_18a.xlsx/file

    Wednesday, August 15, 2018 12:46 AM
  • If I place the formula in i5 the results it will be qty 4 uniques values fromD5 to $D$1.

    Fernando Salgueiro

    Wednesday, August 15, 2018 2:54 AM
  • Hi Fernando Salgueiro,

    Could you provide a sample file about your problem and be a bit more precise to explain your requirement so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.

    You could send this file to our email address:

    ibsofc@microsoft.com

    Note: Please add the URL of the case in the email subject or body, please make sure that you have hidden your private information.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, August 15, 2018 2:58 AM
    Moderator
  • Thanks I already send a sample file to you 


    Fernando Salgueiro

    Wednesday, August 15, 2018 1:14 PM
  • please let me know if you received


    Fernando Salgueiro

    Wednesday, August 15, 2018 1:29 PM
  • Hi Fernando Salgueiro,

    I received your sample file, please try this formula:

    =SUMPRODUCT(1/COUNTIF($B$2:B2,$B$2:B2))

    Hope it's helpful.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, August 17, 2018 9:36 AM
    Moderator
  • Hi,

    You could try this:

    Seed the first item count with 1.

    Then enter the following formula into the next row:

    =B2+IF(ISERROR(MATCH(A3,A$1:A2,   0)),1,0)

    Where b2 is the first value to count, A3 is the second value to count and the column containing the values start at A2 and run down the column

    And copy it down.

    Ethan



    Ethan Strauss

    Friday, August 17, 2018 4:46 PM
  • Hi, 

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Monday, August 20, 2018 9:40 AM
    Moderator