+ Reply to Thread
Results 1 to 9 of 9

Formula for Counting Data with Duplicates

  1. 05-05-2016, 08:20 AM #1
    tentson
    tentson is offline
    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.
    Reply With QuoteRegister To Reply
  2. 05-05-2016, 08:31 AM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,323

    Re: Formula for Counting Data with Duplicates

    Try:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    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

    Reply With QuoteRegister To Reply
  3. 05-05-2016, 08:36 AM #3
    tentson
    tentson is offline
    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
    Reply With QuoteRegister To Reply
  4. 05-05-2016, 08:45 AM #4
    Phuocam
    Phuocam is online now
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,899

    Re: Formula for Counting Data with Duplicates

    Try this in B2:
    =IF(A2<>A1,COUNTIF($A$3:$A3,A2),B1)
    Copy down
    Reply With QuoteRegister To Reply
  5. 05-05-2016, 08:47 AM #5
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,323

    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.
    Reply With QuoteRegister To Reply
  6. 05-05-2016, 08:56 AM #6
    tentson
    tentson is offline
    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.
    Reply With QuoteRegister To Reply
  7. 05-05-2016, 08:58 AM #7
    tentson
    tentson is offline
    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.
    Reply With QuoteRegister To Reply
  8. 05-05-2016, 08:59 AM #8
    Phuocam
    Phuocam is online now
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,899

    Re: Formula for Counting Data with Duplicates

    You're welcome.
    Reply With QuoteRegister To Reply
  9. 05-07-2016, 01:34 AM #9
    tentson
    tentson is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1