Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: count occurences

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to count the number of unique occurrences of 2 feilds being the same in a spreadsheet. I have a list of invoices including dates in one coulumn and account names in another. Some accounts have multiple invoices on the same day. I want to be able to count how many unique customers per day.

    Thanks for the help!!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Lets say that A1:B7 houses the following sample:

    {"Dates","Acc Nums";
    37258,"acc1";
    37258,"acc2";
    37258,"acc1";
    37289,"acc3";
    37289,"acc3";
    37317,"acc1"}

    Funny looking numbers are actually dates.

    Create a unique list of dates of interest in D from D2 on like the one that follows:

    {37258;
    37289;
    37317}

    which is derived from the previous sample of data.

    In E2 array-enter:

    =SUM(N(FREQUENCY(IF(($A$2:$A$7=D2),MATCH($B$2:$B$7,$B$2:$B$7,0)),MATCH($B$2:$B$7,$B$2:$B$7,0))>0))

    In order to array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

    Copy the above formula down as far as needed.

    You'll get the following to see in D and E:

    {37258,2;
    37289,1;
    37317,1}

    which gives you the number of different accounts that are invoiced on a given date.

    Aladin

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It worked with one problem. The answer on 37317 was supposed to be 1, but it returned 0.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-21 09:20, DaKen wrote:
    It worked with one problem. The answer on 37317 was supposed to be 1, but it returned 0.
    DaKen: Go thru instructions & try again. If you still can't get it right, I can send you the example WB.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I still get the same result. I think it may be in how I entered the unigue dates in column d. I entered each unique date in a seperate cell, d2,d3,d4. Maybe you could send the sheet?

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-21 11:05, DaKen wrote:
    I still get the same result. I think it may be in how I entered the unigue dates in column d. I entered each unique date in a seperate cell, d2,d3,d4. Maybe you could send the sheet?
    akyurek@xs4all.nl

    or provide yours.

    Aladin

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This process is working, but it crashes or nearly crashes the program. I have almost 10,000 records in the array. Is there a different way to get the info?

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use a Pivot Table, put your 'DateField' both in as a Row and as Data. Make sure you have 'Count of' selected.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The pivot table is great, but I haven't worked with them much and it returns the total # of records for each date. I need just the number of unique customer records for each date.

    Any help?
    Thanks

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Using Aladin's test data, I produced this Pivot Table.

    {"Cout of Dates",0;
    "Dates","Total";
    37258,3;
    37289,2;
    37317,1;
    "Total General",6}

    Where the "funny lucking numbers" are dates.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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