Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bill_S
 
Posts: n/a
Default 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."
  #2   Report Post  
Bill Martin -- (Remove NOSPAM from address)
 
Posts: n/a
Default

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
  #3   Report Post  
Jason Morin
 
Posts: n/a
Default

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

  #4   Report Post  
Domenic
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to count the cells in a range that have a fill color. Slainteva Excel Discussion (Misc queries) 2 January 19th 05 09:25 PM
Formula to count the cells in a range that have a fill color. Molly F Excel Discussion (Misc queries) 2 January 19th 05 07:15 PM
How do i select different cells to be use in a formula. Manuel Excel Discussion (Misc queries) 1 January 13th 05 04:52 PM
Count non-colored cells Ken G Excel Discussion (Misc queries) 3 January 2nd 05 01:42 PM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM


All times are GMT +1. The time now is 10:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"