Count Of Unique Claim Numbers By Date

1. 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

2. 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. 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

4. Re: Count Of Unique Claim Numbers By Date

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

5. Re: Count Of Unique Claim Numbers By Date

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

6. Re: Count Of Unique Claim Numbers By Date

Originally Posted by FlameRetired
In the meantime this will work in version 2003.
Formula:
`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. 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. Re: Count Of Unique Claim Numbers By Date

Thanks

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