Subtracting one date for another using Countif criteria

[milo1984]

Hi
I have a query which I hope that you can help me with.
I have a formula which counts how many duplicates there are in a list of ID’s. (below)

Please Login or Register  to view this content.

What I need is another formula which basically finds the last duplicate and then subtracts the difference in dates from the first duplicate and the last duplicate to give the number of days.
The formula I am using to find the last duplicate is:

Please Login or Register  to view this content.

The dates are in Column B.
Is this possible?
I would really appreciate some help with this.

Many Thanks in advance.

[milo1984]

Hi

Any help on this would be greatly appreciated.

Many thanks in advance.

[martindwilson]

perhaps a sample workbook?

[milo1984]

Hi

I hope this works. I have attached it below. Basically, I need to subtract the last tx date from the first to get the difference in the number of days (Column A).



test file.xlsx

Many thanks in advance.

[Fotis1991]

In G3 and copy down.

=IF(F3="","",SUM(INDEX($A$2:$A$1000,MATCH(D3&"Last Duplicate",$D$2:$D$1000&$F$2:$F$1000,0)))-SUM(INDEX($A$2:$A$1000,MATCH(D3&"Duplicate # 1",$D$2:$D$1000&$E$2:$E$1000,0))))


ARRAY formula.

[martindwilson]

try it this way

[milo1984]

Thank you both very much for your help. It is what I was looking for.

[Fotis1991]

You are welcome from both of us!