Calculate Number of Days Based on Results in another Column

[pejay]

Hi,

I am trying to find a way of counting the number of days between two dates dependant on the results in the last few rows of another column. In the first example below, because the result in row 6 and 7 of column B is equal, I need to calculate the number of days between these equal numbers. (21/06/2020 - 19/06/2020 = 2 days). As I add more data as in example 2, the previous calculated 2 days will be ignored, as there have been further days when the numbers in B weren't equal. At row 11 number 627 is repeated so 2 days should be retuned. Followed by 11 days and 18 days as the data is added to row 12 and 13.

I've spent a lot of time trying to solve this myself, but am struggling because of getting the formula to reference the last rows where I have data. Any help to point me in the right direction would be gratefully received.

 

[pejay]

 

[Eric W]

Welcome to the forum!

Try:

Book1
	A	B	C
1	Date	Value
2	01/06/2020	551
3	05/06/2020	555
4	09/06/2020	578
5	12/06/2020	581
6	16/06/2020	589
7	19/06/2020	596
8	21/06/2020	596	2
9	24/06/2020	623
10	27/06/2020	624
11	30/06/2020	627
12	02/07/2020	627	2
13	11/07/2020	627	11
14	18/07/2020	627	18
Sheet8

Cell Formulas
Range	Formula
C3:C14	C3	=IF(B3=B2,SUM(A3-A2,C2),"")

 

[pejay]

Hi Eric,

Many thanks for the formula. I think it will work just fine. I've tried it with my small sample and it gives the exact results I need. I just have to see how it will work when I try it with the formula I'm using to pick the last cell from the last row and display the result on anther sheet.

Thanks again,

Peter