Count true / false contunuesly from down to up

Dear Sir / Mam

In my excel sheet1, column B:H has mixed values ​​like "TRUE" and "FALSE", I take the meaning from down line to continues till the top if the last one has "TRUE" then "TRUE" How many values ​​should be counted, if the last one is "FALSE" then how many "FALSE" values ​​should be counted till the top, for that a formula is needed.

COUNT TRUE FALSE.JPG

also attached workbook.

Pls help me.
Thank you in Advance.

mam, i have current 2019 version.

You could also use this array* formula in cell B16:

=13-IF(B14=TRUE,MAX(IF(B2:B14=FALSE,ROW($A$1:$A$13))),MAX(IF(B2:B14=TRUE,ROW($A$1:$A$13))))

*Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

Then copy the formula across to H16.

Hope this helps.

Pete

A shorter version:

=13-MAX(IF(B2:B14=NOT(B14),ROW($A$1:$A$13)))

Still an array formula, so use CSE as previously advised.

Hope this helps.

Pete

Hi Pete_UK , post #8 you Can skip IF again

Formula:

Please Login or Register  to view this content.

Dear Sir/ Mam,

Actually I have big data, if I apply this formula in this sheet then the result is not coming. Is there a type mistake in my formula?

Thank you, getting correct results now..

profile updated

Pls try this formula

Formula:

Please Login or Register  to view this content.

The TRUE and FALSE values in the larger table are actually text values. I used Ctrl-H (Find/Replace) twice to change TRUE to TRUE and FALSE to FALSE and these formulae both gave the correct answer:

=221-MAX(IF(B2:B222=NOT(B222),ROW($A$1:$A$221)))

=221-MAX((B2:B222=NOT(B222))*ROW($1:$221))

Hope this helps.

Pete