+ Reply to Thread
Results 1 to 10 of 10

Unique List/Count Problem

  1. #1
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,682

    Unique List/Count Problem

    Hi,

    I am trying to use a formula which first creates a unique list based on the names in column C and then use a simple countif formula to count the number of occurrences.

    I have it working for the list however the problem is when I have blank rows.

    On my attached file, cells W38:X61 is where I have the list. Can anyone see what I am doing wrong? I wouldn’t want the cells in yellow to be included as the list is to be graphed.

    Paul
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: Unique List/Count Problem

    May be this...

    In X39 Cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag it down...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,682

    Re: Unique List/Count Problem

    Hi,

    That worked for the Count column thanks!

    Is there anyway to remove the "0" from the unique list of names (cell W44)?

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique List/Count Problem

    Try in W39 and copy down, this regular(not ARRAY) formula.

    =LOOKUP(REPT("Z",5),CHOOSE({1\2},"",INDEX(List,MATCH(TRUE,INDEX(ISNA(MATCH(List,W$38:W38,0)),0),0),1)))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,682

    Re: Unique List/Count Problem

    Hi Fortis - this formula contains an error (it doesnt like this part "{1\2}")

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique List/Count Problem

    Strange..Paul. What do you see in my attachment?
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,682

    Re: Unique List/Count Problem

    Strange - the file you provided worked!

    Can you help with graphing the list? The problem I am experiencing is that it doesn’t like the rows which do not contain data.

    I have googled the problem and I need it to ignore blank cells however because they contain formula they are not blank.

    If I select the chart range W38:X43 the chart works as this contains data. However if I select the range W38:X61 it doesn’t work. I would need to use this as the range as the list would be dynamic.

    I have re-uploaded the file with the working formulas.
    Attached Files Attached Files

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique List/Count Problem

    I have no idea..I don't work with graphs, so i don't know much for these..

    I just modified your List(in column C) to be dynamic..but i see nothing different. Sorry..

  9. #9
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,682

    Re: Unique List/Count Problem

    Ok, thanks Fortis. I will mark this post as solved

  10. #10
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique List/Count Problem

    .......................

    .
    Fo(r)tis

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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