+ Reply to Thread
Results 1 to 4 of 4

to count duplicated cells in multiple columns

  1. #1
    Registered User
    Join Date
    01-17-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    15

    to count duplicated cells in multiple columns

    Dear all excel experts,

    I have another question about counting duplicated cells in multiple columns and here I attached the file for reference.

    1) I want to find out how many cells are appeared in these 3 columns ?
    2) And its sum value.

    Thanks in advance
    kary
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-08-2016
    Location
    kuala lumpur malaysia
    MS-Off Ver
    365
    Posts
    100

    Re: to count duplicated cells in multiple columns

    to count, you may use this formula =COUNTIF($B$5:$D$9,"a")
    [change "a" to any other alphabet that you want to count]

  3. #3
    Registered User
    Join Date
    01-17-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    15

    Re: to count duplicated cells in multiple columns

    hi Wanmuhd,

    the file contains just few rows, but my original data contains thousand rows , so i cant type one by one ....

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: to count duplicated cells in multiple columns

    h5=SUMPRODUCT((COUNTIF(C5:C9,D5:D9)>0)*(COUNTIF(B5:B9,D5:D9)>0))
    h8=SUMPRODUCT((COUNTIF(C5:C9,D5:D9)>0)*(COUNTIF(B5:B9,D5:D9)>0)*($E$5:$E$9))
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count de-duplicated aggregates
    By Delta729 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-18-2016, 06:45 PM
  2. [SOLVED] Sort and Count Unique List Across Multiple Columns - Student Count
    By BuntyMac in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 08-17-2014, 07:00 AM
  3. [SOLVED] Compare two sheets with multiple cells to see if the data is duplicated on the 2nd sheet
    By Lucille Boshoff in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 07:59 AM
  4. Assign a duplicated cell to multiple other cells
    By Nikolas1988 in forum Excel General
    Replies: 3
    Last Post: 06-12-2014, 05:49 AM
  5. Subtracting duplicated text between cells of two columns
    By sp86 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 10-09-2013, 10:26 AM
  6. I need to count how many times a name is duplicated?
    By aw73 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-17-2013, 06:38 AM
  7. Replies: 1
    Last Post: 11-20-2010, 05:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1