Calculate the number of consecutive results

Hello,

I hope you can give me a hand; I would greatly appreciate it. I hope I can explain myself well.

I have a file with some columns, in one I have for the "Warehouse 1" three locations (Madrid, Sevilla and Barcelona), and for "Warehouse 2" the same places, although internally they are different warehouses. Then in two other columns, I have codes that correspond to each locality, according to each Warehouse. Everything is sorted by date.

What I would like to calculate is the number of consecutive results that are produced per code "A" of each location, depending on the store, taking into account the previous rows, not the current cell containing the formula. The result would be displayed in several columns, on the one hand looking for only at Warehouse 1, for another looking for only at Warehouse 2, and finally for the two warehouses.

If in the row of the cell, the immediately preceding one is not "A", then the result would be zero, even if there are older ones that do have "A". If, for example, the three previous rows have "A", i.e., they are consecutive, the result would be three, assuming that the fourth backwards would not be "A".

I look for the previous cells that are consecutive, always containing "A", until it contains it, and I get the total result of the consecutive ones.


I send the file with an example, which will surely be understood better than my explanation and in red colour with the result that should come out.

Thank you very much for your help,

Kind regards,

Fran