Count if array contains cells of a certain value
 

This is an example of my table:
Row No.
1 Value Count
2 FALSE 0
3 FALSE 0
4 TRUE 0 (=countif($B$2:B4,TRUE))
5 FALSE 1 (=countif($B$2:B5,TRUE))
6 FALSE 0
7 TRUE 0
8 FALSE 1 (=countif($B$6:B8,TRUE))
9 TRUE 1 (=countif($B$6:B9,TRUE))

In column C, I want to count how many times the value "True" appears in
column B. But I only want to count from the array that stops the row ABOVE
the current one. The starting cell of the array will remain fixed for 4 rows
then it changes. In my example above, for Row No. 2-5, the starting cell is
$B$2. However, for Row No. -9, the starting cell is $B$6.

I want to copy this formula down 2000+ rows without having to manually
change the starting cell. Can this be done?

one way to do it would be to use an equation such as
=countif($A$1:A5,"True")-Count($A$1:A4,"True")
I am not sure from your writeup and example whether you would want this one
in B5 or B6 (B6 I think)
Anyway you can copy this down to end of your data and get zeros and ones.

"Melissa" wrote:

This is an example of my table:
Row No.
1 Value Count
2 FALSE 0
3 FALSE 0
4 TRUE 0 (=countif($B$2:B4,TRUE))
5 FALSE 1 (=countif($B$2:B5,TRUE))
6 FALSE 0
7 TRUE 0
8 FALSE 1 (=countif($B$6:B8,TRUE))
9 TRUE 1 (=countif($B$6:B9,TRUE))

In column C, I want to count how many times the value "True" appears in
column B. But I only want to count from the array that stops the row ABOVE
the current one. The starting cell of the array will remain fixed for 4 rows
then it changes. In my example above, for Row No. 2-5, the starting cell is
$B$2. However, for Row No. -9, the starting cell is $B$6.

I want to copy this formula down 2000+ rows without having to manually
change the starting cell. Can this be done?

It's v difficult to explain my question, but I'll try harder!
Your formula gives the same result as mine. In your example, if I were to
copy the formula down the 2000 rows, I will get a running total of all "TRUE"
in my column C. This is because my starting cell is $A$1.

If we refer back to my example table below, you can see that my formula for
Row 4 is different from that for Row 8, in that the starting cell is
different. So for Rows 1-4, the starting cell is $B$2 while for rows 6-9,
the starting cell is $B$6.

This change in starting cell occurs at regular intervals of 4 rows (in my
example table).

Any hope here?

"bj" wrote:

one way to do it would be to use an equation such as
=countif($A$1:A5,"True")-Count($A$1:A4,"True")
I am not sure from your writeup and example whether you would want this one
in B5 or B6 (B6 I think)
Anyway you can copy this down to end of your data and get zeros and ones.

"Melissa" wrote:

This is an example of my table:
Row No.
1 Value Count
2 FALSE 0
3 FALSE 0
4 TRUE 0 (=countif($B$2:B4,TRUE))
5 FALSE 1 (=countif($B$2:B5,TRUE))
6 FALSE 0
7 TRUE 0
8 FALSE 1 (=countif($B$6:B8,TRUE))
9 TRUE 1 (=countif($B$6:B9,TRUE))

In column C, I want to count how many times the value "True" appears in
column B. But I only want to count from the array that stops the row ABOVE
the current one. The starting cell of the array will remain fixed for 4 rows
then it changes. In my example above, for Row No. 2-5, the starting cell is
$B$2. However, for Row No. -9, the starting cell is $B$6.

I want to copy this formula down 2000+ rows without having to manually
change the starting cell. Can this be done?