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

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

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

 A B C 2019/01/02 WL01 50 2019/01/12 WL02 120 2019/01/06 WL06 30 2019/01/14 WL01 125 2019/01/23 WL02 135 2019/01/31 WL06 65  Reply With Quote

2. 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)  Reply With Quote

3. 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?  Reply With Quote

4. 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  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•