+ Reply to Thread
Results 1 to 3 of 3

Count Repeated Items Once

  1. #1
    Registered User
    Join Date
    02-18-2010
    Location
    T.O.
    MS-Off Ver
    MS 365
    Posts
    57

    Count Repeated Items Once

    Here's my example:

    ColumnA Column B
    1 9FT
    1 9FT
    1 9FT
    1 9FT
    2 7FT
    2 7FT
    2 7FT
    2 10FT
    3 10FT

    I need a statement to count 1 = 4, 2 = 4, 3 = 1. Something without having to sum then divide by count.

    Hope that makes sense? Seems simple to me but I've been stuck for several minutes now.

    Thanks in advance!!

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count Repeated Items Once

    Maybe this

    in D1 and copy down

    =COUNTIF(A$1:A$9,C1)


    Row\Col
    A
    B
    C
    D
    1
    1
    9FT
    1
    4
    2
    1
    9FT
    2
    4
    3
    1
    9FT
    3
    1
    4
    1
    9FT
    5
    2
    7FT
    6
    2
    7FT
    7
    2
    7FT
    8
    2
    10FT
    9
    3
    10FT
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Count Repeated Items Once

    You can use a Pivot Table to do what you want without using a formula at all. To update the table, right click in the Pivot Table and choose Refresh.

    I entered your data into a table and used that table to produce the Pivot Table. As you enter data in the table, the data in the Pivot Table is easily updated with the REFRESH command as described above. You can change the formatting of the data table to suit.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Replies: 8
    Last Post: 06-21-2013, 04:35 PM
  2. Count Repeated Items Once
    By longbow007 in forum Excel General
    Replies: 4
    Last Post: 06-29-2012, 07:33 AM
  3. How to count items that are not repeated in one row?
    By Lewis Koh in forum Excel General
    Replies: 2
    Last Post: 04-24-2011, 11:07 PM
  4. Excel 2007 : Count of repeated items in excel sheet
    By sashi4565 in forum Excel General
    Replies: 1
    Last Post: 12-22-2009, 11:12 AM
  5. Non repeated use of items in a lookup table?
    By groethem in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2008, 11:09 PM

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