Count Unique Values with Duplicates in Multiple Columns

Greetings everyone!

I want to provide a function which will count the number of applications greater than or equal to 25 days over due date while only considering the most recent individual "Application Type" per "Company No.". By most recent I mean that an "Updates" over rides an "Originals". If an application has multiple "Updates" only consider the days over due date of the most recent "Update".

I have been trying various iterations of the following function: =SUM(IF(FREQUENCY(IF(MATCH(A3:A8,A3:A8,0))))),ROW(A3:A8)-ROW(A3)+1),1)). However, as stated above I am trying to count the most recent application and this function is not quite fitting the bill.

I have attached a sample file which shows the highlighted results I would like the function to count.

Any help is greatly appreciated! Please let me know if anything is unclear.

See if is this you want
IN G3 use the following formula

Formula:

Please Login or Register  to view this content.

and copy down.
The lines where the application is over due date >=25 are marked with the days over due

Thank you for your reply, Jose. The function you provided works; however, my data set has over 17000 different company numbers and it appears that you equation can only accommodate the three companies in the sample sheet.

Hi @sTeezZy
You can use for as many companies as you wish. Adjust the formula to your range
=IF(A3&B3&TEXT(AGGREGATE(14,6,(--($E$3:$E$18000>=25)*($A$3:$A$18000=A3)*($B$3:$B$18000=B3)*($D$3:$D$18000)),1),"dd-mm-yyyy")=A3&B3&TEXT(D3,"dd-mm-yyyy"),E3,"")

Jose, I added a fourth company with updated application which was over the 25 limit; however, when I applied an update to the original application for the fourth company, which was within the 25 day limit, the function counted the original. The update needs to over rider the original. I have attached a copy to show what I attempted to explain here. Thanks!

I believe the simplest way to accomplish this task would be to have excel first view the company number, then view the application types associated with that company number, and then determine what is the day over on the most recent update of each application and count the applications where the day over is over the 25 limit.

Hi
Try this
=IF(A3&B3&TEXT(AGGREGATE(14,6,(($A$3:$A$1800=A3)*($B$3:$B$1800=B3)*($D$3:$D$1800)),1),"dd-mm-yyyy")=A3&B3&TEXT(D3,"dd-mm-yyyy"),IF(E3>=25,E3,""),"")

Formula:

Please Login or Register  to view this content.

Jose, you last function works great! Thank you for all your help.

If you wouldn't mind could you explain what the AGGREGATE function is doing as we as the A3&B3&TEXT string?

Thanks again!

Hi @sTeezZy
What I do is concatenate A3,B3 and the larger date in format "dd-mm-yyyy" in Column D3 where the rows has same value in column A as A3 and same value in column B as B3.
larger --> AGGREGATE(14,6, ... (same as LARGE but we can operate with ranges (14 is LARGE, 6 is Ignore error)) --> We get the larger date XX of A3 and B3 that is or is not the same as D3
TEXT(AGGREGATE....;"dd-mm-yyyy") ---> Convert the date in that format
If in the row n An=A3, Bn=B3 and Dn=XX Then IF En is greater or equal to 25 Mark with En Else don't mark!

Don't forget to mark this thread as SOLVED.
Best regards