need formula to count cells below
 

Column E on my spreadsheet contains a series of values TRUE and FALSE in no
particular order (they are the results of a formula, the details of which I
won't bore anybody with.) I need a formula in column F that checks if the
corresponding cell in column E contains "TRUE" and if so, it counts the
number of "TRUE's" below it before the next occurrence "FALSE."

Bill_S wrote:
Column E on my spreadsheet contains a series of values TRUE and FALSE in no
particular order (they are the results of a formula, the details of which I
won't bore anybody with.) I need a formula in column F that checks if the
corresponding cell in column E contains "TRUE" and if so, it counts the
number of "TRUE's" below it before the next occurrence "FALSE."

You can do it easily with two columns. For example if the first element
is at E2:

[F2] = IF(E2,F3+1,0)
[G2] = IF(F1,0,F2)

And then copy these formulas down the length of your column.

This expects your E1 column to be initialized FALSE or 0. You can then
set the F column to some tiny font and close up the column width so it
isn't distracting.

To do it with a single column may require a bit of VBA to create a
custom function. Perhaps someone else here will have a better suggestion.

Bill

In F1:

=IF(E1,MIN(IF(E2:$E$20=FALSE,ROW(INDIRECT(ROW()
+1&":"&20))-ROW()-1)))

Press ctrl/shift/enter and fill down. Change $E$20 to the
last cell in your col.

HTH
Jason
Atlanta, GA

-----Original Message-----
Column E on my spreadsheet contains a series of values
TRUE and FALSE in no
particular order (they are the results of a formula, the
details of which I
won't bore anybody with.) I need a formula in column F
that checks if the
corresponding cell in column E contains "TRUE" and if
so, it counts the
number of "TRUE's" below it before the next
occurrence "FALSE."
.

Try the following...

F1, copied down:

=IF(E1=TRUE,IF(COUNTIF(E1:$E$100,FALSE),MATCH(0,N( OFFSET(E1:$E$100,ROW(E1
:$E$100)-MIN(ROW(E1:$E$100)),0,1)),0)-2,COUNTIF(E1:$E$100,TRUE)-1),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
Bill_S wrote:

Column E on my spreadsheet contains a series of values TRUE and FALSE in no
particular order (they are the results of a formula, the details of which I
won't bore anybody with.) I need a formula in column F that checks if the
corresponding cell in column E contains "TRUE" and if so, it counts the
number of "TRUE's" below it before the next occurrence "FALSE."