need formula to count cells below

B

Bill_S

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."
 
G

Guest

Bill_S said:
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."
Click to expand...

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
 
J

Jason Morin

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
 
D

Domenic

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!
 
Top