+ Reply to Thread
Results 1 to 8 of 8

Count Of Unique Claim Numbers By Date

  1. #1
    Registered User
    Join Date
    01-18-2012
    Location
    Wigan, England
    MS-Off Ver
    Excel 2016
    Posts
    14

    Count Of Unique Claim Numbers By Date

    Hi all,

    I've been searching this forum but can't find anything that matches my request exactly.

    I've attached what I'd like the result to be in the yellow box.

    I'd like a formula to check the date and count unique claim numbers and add the total next to the date.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Count Of Unique Claim Numbers By Date

    =SUM(--IFERROR((MATCH($F$2:$F$11,IF($A$2:$A$11=$J2,$F$2:$F$11),)=ROW($2:$11)-1),)) array formula

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Count Of Unique Claim Numbers By Date

    Try this array formula in Cell K2

    =SUM(IF(FREQUENCY(IF(A2:A11=J2,MATCH(F2:F11,F2:F11,0)),ROW(A2:A11)-ROW(A2)),1,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,258

    Re: Count Of Unique Claim Numbers By Date

    Administrative note

    Still using XL2003 as indicated in your profile?
    If not,perhaps update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to? Members tailor answers based on your Excel version.
    Thanks
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,608

    Re: Count Of Unique Claim Numbers By Date

    In the meantime this will work in version 2003.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  6. #6
    Registered User
    Join Date
    01-18-2012
    Location
    Wigan, England
    MS-Off Ver
    Excel 2016
    Posts
    14

    Re: Count Of Unique Claim Numbers By Date

    Quote Originally Posted by FlameRetired View Post
    In the meantime this will work in version 2003.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks so much for everyones help this is nearly what I'm looking for, the only issue I've had is if theres less claim numbers than the 11 rows in the formula i get the #DIV error, there may be 1 or 101 claims in a day or week, how can I adjust the formula to ignore blank rows?

    Also I've amended my excel version to 2016, I didn't realise.

    Thanks

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,212

    Re: Count Of Unique Claim Numbers By Date

    Perhaps
    =SUMPRODUCT((J2=$A$2:$A$12)/COUNTIF($F$2:$F$12,$F$2:$F$12&""))

    I added 12 to test blanks

  8. #8
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,258

    Re: Count Of Unique Claim Numbers By Date

    Administrative Note:

    Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
    Use the "Quick reply" instead
    Thanks

+ 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. Macro help creating claim numbers in a workorder
    By whitterz45 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2019, 03:19 PM
  2. Count unique item numbers
    By birgiir in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-10-2019, 10:22 PM
  3. Replies: 1
    Last Post: 10-05-2018, 02:36 PM
  4. [SOLVED] COUNT unique reference numbers based dates in a specified date range
    By 3PDM in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-14-2012, 08:50 AM
  5. Count unique numbers with an AND condition????
    By ajajmannen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-05-2007, 09:15 AM
  6. summary count of unique numbers
    By Dave Edge in forum Excel General
    Replies: 5
    Last Post: 11-11-2005, 08:10 PM
  7. Replies: 5
    Last Post: 02-28-2005, 01:51 PM

Tags for this Thread

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