Countif a cell equals "x" and cell above is "y"

Hi all,

can anyone come up with an array formula which would count the occurancy of a word(or value) being below a specified word(or value)?

For example count if "A" is below "C" in the following range:

A
B
C
A
C
A
B
C

The result would equal 2 because "A" can be found twice directly under "C".

Thanks!

Assume that the start cell of your data is A1 and A8 is the end cell, so your data is from A1:A8.

In B1 cell

Formula:

Please Login or Register  to view this content.

Works like a charm Sixthsense! Thank you. Would you be able to guide me how to make it work with a formula like this?

Please Login or Register  to view this content.

Your version is "cleaner" obviously, but I'd like to grasp how to do those things with an array formula as well.

Thanks!

Glad it helps you and thanks for the feedback and rep

If you would like to convert it as array formula then make it like the below formula

Formula:

Please Login or Register  to view this content.

* operates as AND operator in ARRAY FORMULA