# 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

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))

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?

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

#### Posting Permissions

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