Find 1st and last specific text between 2 dates and subtract thie adjacent value

Sam67

New member
Joined
Apr 29, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Excel 2010
Hi, I'm trying to find a formula that find the first instance in January when WL01(column B)occur and the last time WL01 occur (column B) and then subtract the last instance (column C) value form the first instance value(column C) from each other, 125-50=75

ABC
2019/01/02WL0150
2019/01/12WL02120
2019/01/06WL0630
2019/01/14WL01125
2019/01/23WL02135
2019/01/31WL0665

 
Hi and welcome
try this =INDEX(C1:C6,AGGREGATE(14,6,(1/((B1:B6=B1)))*ROW(B1:B6),1))-INDEX(C1:C6,AGGREGATE(15,6,(1/((B1:B6=B1)))*ROW(B1:B6),1))

( should be adapted if you do not start with row 1)
 
Hi, thanks it works!!


Is there a way I can tweak that formula to let it look between 2019/01/01 and 2019/01/31 and between 2019/02/01 and 2019/02/28 using dates ..(and other months) if I put the start and end dates of all month in a column?
 
Try formula
Code:
=INDEX(C1:C6;AGGREGATE(14;6;(1/((A1:A6=$A$4)))*ROW(A1:A6);1))-INDEX(C1:C6;AGGREGATE(15;6;(1/((A1:A6=$A$1)))*ROW(A1:A6);1))
$A$4 is last Date
$A$1 is first Date
 
Back
Top