Joe asks:

I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0" .These alternate across 1800 colunms of data.

My question: how do I count the number of groupings of each? In other words, across those 1800 columns, how many arrays of value 1 and how many arrays of value 0 do I have?
Thanks.
Joe

Answer:

I am only using six columns, in this example.

I replaced (Ctrl + H) all zeros in the range with value 2. Why? If I compare an empty cell ("") with 0 (zero) the formula returns TRUE. I want the formula to return FALSE.

Here is a picture of a cell range with random values. The first row has 1 group of 1´s and 1 group of 2´s. The second row has 2 groups of 1's and 3 groups of 2's and so on. I counted the groups in the range and there are 14 groups of 2's and 12 groups of 1's.

Lets create a formula!

Array formula in cell I2:

=SUM((B1:G7<>A1:F7)*(B1:G7=1))

Array formula in cell J2:

=SUM((B1:G7<>A1:F7)*(B1:G7=2))

How to create an array formula

  1. Select cell I2
  2. Paste formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

Explaining array formula in cell I2

Step 1 - Compare cell range B1:G7 with A1:F7

B1:G7<>A1:F7

becomes

{1, 1, 1, 1, 2, 2;2, 1, 2, 2, 1, 2;2, 1, 2, 2, 2, 1;2, 1, 1, 2, 1, 1;2, 2, 2, 1, 2, 1;2, 1, 2, 2, 1, 2;1, 1, 1, 1, 1, 2} <> {0, 1, 1, 1, 1, 2;0, 2, 1, 2, 2, 1;0, 2, 1, 2, 2, 2;0, 2, 1, 1, 2, 1;0, 2, 2, 2, 1, 2;0, 2, 1, 2, 2, 1;0, 1, 1, 1, 1, 1}

and returns

{TRUE, FALSE, FALSE, FALSE, TRUE, FALSE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, FALSE, TRUE;TRUE, TRUE, FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, FALSE, FALSE, FALSE, FALSE, TRUE}

Step 2 - Count only 1's

(B1:G7<>A1:F7)*(B1:G7=1)

becomes

{TRUE, FALSE, FALSE, FALSE, TRUE, FALSE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, FALSE, TRUE;TRUE, TRUE, FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, FALSE, FALSE, FALSE, FALSE, TRUE}*(B1:G7=1)

becomes

{TRUE, FALSE, FALSE, FALSE, TRUE, FALSE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, FALSE, TRUE;TRUE, TRUE, FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, FALSE, TRUE, TRUE;TRUE, FALSE, FALSE, FALSE, FALSE, TRUE}*{TRUE, TRUE, TRUE, TRUE, FALSE, FALSE;FALSE, TRUE, FALSE, FALSE, TRUE, FALSE;FALSE, TRUE, FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, TRUE, FALSE, TRUE, TRUE;FALSE, FALSE, FALSE, TRUE, FALSE, TRUE;FALSE, TRUE, FALSE, FALSE, TRUE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, FALSE}

and returns

{1, 0, 0, 0, 0, 0;0, 1, 0, 0, 1, 0;0, 1, 0, 0, 0, 1;0, 1, 0, 0, 1, 0;0, 0, 0, 1, 0, 1;0, 1, 0, 0, 1, 0;1, 0, 0, 0, 0, 0}

Step 3 - Sum values

SUM((B1:G7<>A1:F7)*(B1:G7=1))

becomes

SUM({1, 0, 0, 0, 0, 0;0, 1, 0, 0, 1, 0;0, 1, 0, 0, 0, 1;0, 1, 0, 0, 1, 0;0, 0, 0, 1, 0, 1;0, 1, 0, 0, 1, 0;1, 0, 0, 0, 0, 0})

and returns 12 in cell I2.

Download excel *.xlsx file

Count groups of a value.xlsx