Formula to Output Result Based on Consecutive Cells Containing X

Hello everyone,

I am new to this forum, apologies if this has been posted before.

I am looking for a formula for the below:

Scenario one: Let's say A1 is populated, A2 is populated, A3 is blank and A4 is populated and A5 is populated. I am looking for a formula that will output a value of 2 in cell A6. Basically looking for an output that only takes into consideration consecutive cells.

Another scenario: B1 is blank, B2 is populated, B3 is populated, B4 is populated, B5 is blank. I am looking for an output of 2 since there are two consecutives (B2, B3 and B3, B4)

Just as an fyi, I am populating these cells with an insert symbol function of 'X'.


Thanks in advance!

Welcome to the forum!

Your thread title does not comply with the forum rules, so I have changed it for you this time. Please make sure that you use more explicit thread titles in future. Thanks!

Please try at A6 and drag to the right
=sumproduct((a1:a4<>"")*(a1:a4=a2:a5))

Data in A1:A5

a. (a1:a4<>"") give 4 values of True False, True if not blank
b. (a1:a4=a2:a5) check a1=a2, … ,a4=a5 give 4 values of True False, True if cell and cell next row has the same value
c. (a1:a4<>"")*(a1:a4=a2:a5) give 4 values of (True,False)*(True,False) True if not blank and ell and cell next row has the same value. True*True = 1, True*False = 0

Sumproduct(c.) for count only True*True

Could you please upload your file > Go Advanced > Manage Attachments

Please try CF formula applies to A1:B11

=COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1)>1

Hey Bo_Ry! Thanks again for your help yesterday. I believe this could be the last piece of the puzzle for my project thanks to you.
In the attached file, can you help me with the formula to highlight consecutive duplicate cells (including the first occurrence) ignoring the blank cells? I have defined my criteria in Column S for the respective rows.

Edit:

For Row 1: I need I1 and J1 highlighted
For Row 2: I need E2 highlighted as well along with the rest
For Row 3: I need F3 highlighted as well along with the rest

Please try CF formula applies to E1:Q9

=(D1=E1)*(E1="✖")

Try
=((D1=E1)+(E1=F1))*(E1="✖")