Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
How do i select different cells to be use in a formula. | Excel Discussion (Misc queries) | |||
Count non-colored cells | Excel Discussion (Misc queries) | |||
Count number of shaded cells | Excel Discussion (Misc queries) |