This is going to be an odd request, apologies! I would like to be able to count the number of blank cells that are directly above a “Banana” cell. This only needs to happen in one column. In the example below, the number 2 should be returned, because there are 2 blank cells right above a banana cell. I bolded those 2 blank cells. Let me know if you have an idea for counting this!
| A | |
|---|---|
| 1 | Apple |
| 2 | Banana |
| 3 | (blank) |
| 4 | Banana |
| 5 | (blank) |
| 6 | Apple |
| 7 | (blank) |
| 8 | (blank) |
| 9 | Banana |
I’m imagining a sumproduct/index/match formula, but at a bit of a loss for how exactly to accomplish this. Thank you!
