locked
How to count the items in the same column RRS feed

  • Question

  • There are many duplicate items in one specific column such as:

    https://ibb.co/hWYmp5T

    There is only 4 different message IDs but there are some duplicate rows. Is there any method to count the message IDs in this column? There are 2000+ rows for this column. Thanks.

    Thursday, July 16, 2020 6:06 AM

Answers

  • Hi,

    Try this formula:

    =SUM(IF(FREQUENCY(MATCH($K$2:$K$25,$K$2:$K$25,0),MATCH($K$2:$K$25,$K$2:$K$25,0)),1,0))

    Hope it's helpful.

    This “Excel IT Pro Discussions” Forum will be migrating to a new home on Microsoft Q&A, please refer to this sticky post for more details.

    Regards,

    Emi Zhang


    "Office" forums will be migrating to a new home on Microsoft Q&A !
    We invite you to post new questions in the "Office" forums' new home on Microsoft Q&A !

    • Marked as answer by Moonnoom Thursday, July 16, 2020 8:36 AM
    Thursday, July 16, 2020 6:51 AM

All replies

  • Hi,

    Try this formula:

    =SUM(IF(FREQUENCY(MATCH($K$2:$K$25,$K$2:$K$25,0),MATCH($K$2:$K$25,$K$2:$K$25,0)),1,0))

    Hope it's helpful.

    This “Excel IT Pro Discussions” Forum will be migrating to a new home on Microsoft Q&A, please refer to this sticky post for more details.

    Regards,

    Emi Zhang


    "Office" forums will be migrating to a new home on Microsoft Q&A !
    We invite you to post new questions in the "Office" forums' new home on Microsoft Q&A !

    • Marked as answer by Moonnoom Thursday, July 16, 2020 8:36 AM
    Thursday, July 16, 2020 6:51 AM
  • Thank you Emi, it works!!
    Thursday, July 16, 2020 8:36 AM