+ Reply to Thread
Results 1 to 9 of 9

Formula for Counting Data with Duplicates

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    saudi
    MS-Off Ver
    Excel 2007
    Posts
    8

    Formula for Counting Data with Duplicates

    Hi everyone, please help me solve this problem. I have a spreadsheet that I am trying to automate the counting but it seems not working.

    I am using countif to automate the counting but it seems not working.

    =COUNTIF($A$3:$A4,A3)
    I have attached a sample of what I am after. In the data, the red ones in column B are wrong (using the countif formula), the correct count should be like in column C.

    Untitled.png

    If it is possible that anyone could help me out, that would be great.

    Cheers,
    Attached Images Attached Images
    Last edited by tentson; 05-05-2016 at 08:30 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,016

    Re: Formula for Counting Data with Duplicates

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    09-27-2012
    Location
    saudi
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula for Counting Data with Duplicates

    Quote Originally Posted by TMS View Post
    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    thanks for the quick reply. below is the result.

    Untitled.png

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,909

    Re: Formula for Counting Data with Duplicates

    Try this in B2:
    =IF(A2<>A1,COUNTIF($A$3:$A3,A2),B1)
    Copy down

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,016

    Re: Formula for Counting Data with Duplicates

    Your data appears to start in row 2, not row 3, so it would be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-27-2012
    Location
    saudi
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula for Counting Data with Duplicates

    Untitled.png

    Quote Originally Posted by Phuocam View Post
    Try this in B2:
    =IF(A2<>A1,COUNTIF($A$3:$A3,A2),B1)
    Copy down
    got the result I wanted, i just mistakenly entered the formula in column E

    Quote Originally Posted by Phuocam View Post
    Try this in B2:
    =IF(A2<>A1,COUNTIF($A$3:$A3,A2),B1)
    Copy down
    the result is in column D
    Last edited by tentson; 05-05-2016 at 09:00 AM.

  7. #7
    Registered User
    Join Date
    09-27-2012
    Location
    saudi
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula for Counting Data with Duplicates

    Quote Originally Posted by Phuocam View Post
    Try this in B2:
    =IF(A2<>A1,COUNTIF($A$3:$A3,A2),B1)
    Copy down
    got it, thanks very much.

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,909

    Re: Formula for Counting Data with Duplicates

    You're welcome.

  9. #9
    Registered User
    Join Date
    09-27-2012
    Location
    saudi
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula for Counting Data with Duplicates

    Quote Originally Posted by Phuocam View Post
    You're welcome.
    sir, from the image below. i would like to know if it is possible to get the value in manual column using a formula? the formula column was using the formula you have given me.

    Untitled.png

+ 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 formula without counting duplicates
    By jason4444 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-14-2016, 10:07 PM
  2. Counting duplicates except rows with specific data
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2013, 10:10 AM
  3. [SOLVED] Counting data duplicates across columns
    By sahsinha in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-24-2013, 02:12 PM
  4. Counting Frequency of duplicates for each unique value and corresponding data
    By fredman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2009, 12:55 AM
  5. Counting Data in a 2-column Array (with Duplicates)
    By gwoaai2 in forum Excel General
    Replies: 1
    Last Post: 08-23-2008, 11:42 AM
  6. counting cells with data without counting duplicates
    By labettis in forum Excel General
    Replies: 2
    Last Post: 11-05-2007, 12:10 PM
  7. counting duplicates within array formula?
    By etmac in forum Excel General
    Replies: 5
    Last Post: 09-05-2006, 04:45 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