Help with IF/ LOOKUP functions

[eilzmillar]

I have a problem where I need to be able to calculate filter life over multiple days the problem I'm having is that the filters are lasting anywhere between 1 and 13 days.

Is there a way in which I could use a lookup formula to sum the flow treated per filter even if the number of days between filter changes is different?

In the attached spreadsheet I've used an IF function (in column D) cell D3 reads =IF(C3=1,(B3-B2),"") but have to manually change the values for column B depending on the number of days the filter is lasting

Book1.xlsx

Would a LOOKUP function be suitable for this but not sure how that would look like?

I'm hoping that someone may be able to help make this more automated

[benishiryo]

welcome to the forum, eilzmillar. try in D3:
=IF(C3=1,(B3-LOOKUP(2,1/(B$2:B2<>""),B$2:B2)),"")

[eilzmillar]

Thanks Benishiryo

That works great!