date Subtraction with serial numbers as a reference

[queysoft]

Hello - Stuck with this one - I'm sure its simple and i am once again overthinking......

So, Column A = List of serial numbers (some unique, some duplicated several times) - ALL IN ORDER.
Column B = Date

What I want, is a formula that subtracts the LAST date from the FIRST date for each serial number to get a max number of days for each serial number.

The sheet is sorted by serial and then by date descending.

So the formula would look at the serials, and pick out the last and first date for each serial number. For the unique serials of course the return would be a zero.

Any ideas?

 

[Fluff]

How about

=IF(COUNTIFS(A$2:A2,A2)=1, AGGREGATE(14,6,$B$2:$B$100/($A$2:$A$100=A2),1)-B2,"")

 

[etaf]

you are showing 2016 version in profile
so a MINIFS and a MAXIFS maybe the way to go by ID number

As its an image i cannot test - so may setup if i have time or use XL2BB to post a sample - see signature / menu

 

[Fluff]

@etaf both minifs & maxifs are only available with 2019 or newer.

 

[etaf]

Thanks @Fluff, I thought 2016 version had them as i searched google to see

MAXIFS and MINIFS in Excel

Use MAXIFS and MINIFS in Excel 2016 or later to find the maximum and minimum value based on one criteria or multiple criteria.

www.excel-easy.com

Cant trust the internet (where have i heard that before)

 

[queysoft]

Good work chaps! The first solution works a treat…..Correct also about MAXIFS not working. Work won’t shell out for O365 nor upgrade me, yet I’m asked to do stuff like this. Plus of course GDPR prevents me using my personal laptop. Hey ho.

 

[queysoft]

Also why it was just a screen shot and not the sheet (download permissions etc to install what’s needed)

 

[Fluff]

Glad we could help & thanks for the feedback.