Author: Oscar Cronquist Article last updated on June 15, 2019

Joe asks:

I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0". These alternate across 1800 columns 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 using a much smaller size to more easily show how the formulas work and what the functions used return.

There are 14 groups of value 0 (zero) per row in cell range B2:G8 and 12 groups of value 1. The image above shows 14 blue circles around each group of value 0 to demonstrate how the formula below works.

Regular formula in cell B11:

=SUMPRODUCT((B2:G8<>A2:F8)*(B2:G8=1))

Array formula in cell C11:

=SUM((B2:G8<>IF(ISBLANK(A2:F8),"",A2:F8))*(B2:G8=0))

This formula is different because Excel evaluates a blank cell as 0 (zero), this makes it impossible to use the same formula as in cell B11.

How to create an array formula

  1. Copy formula above.
  2. Select cell C11 with mouse.
  3. Paste formula (keyboard shortcut CTRL + v)
  4. Press and hold CTRL + SHIFT simultaneously.
  5. Press Enter once
  6. Release all keys.

Explaining array formula in cell C11

The formula in cell C11 is slightly more complicated than the formula in cell B11, they are however so similar that I will only explain the formula in cell C11.

Step 1 - Check if cell is blank

The ISBLANK function returns TRUE if the cell is blank and FALSE if not blank, no surprises here.

ISBLANK(A2:F8)

becomes

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

and returns

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

Step 2 - If blank return a blank

The IF function returns a blank if the logical expression ISBLANK(A2:F8) returns TRUE and returns the number itself if FALSE.

IF(ISBLANK(A2:F8),"",A2:F8)

becomes

IF({TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE},"",A2:F8)

becomes

IF({TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE}, "", {0, 1, 1, 1, 1, 0; 0, 0, 1, 0, 0, 1; 0, 0, 1, 0, 0, 0; 0, 0, 1, 1, 0, 1; 0, 0, 0, 0, 1, 0; 0, 0, 1, 0, 0, 1; 0, 1, 1, 1, 1, 1})

and returns

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

These two steps are necessary due to Excel not being able to compare a 0 (zero) with an empty cell. Excel evaluates an empty cell as 0 (zero).

Step 3 - Compare arrays

This step compares values in order to identify groups of repeated values.

(B2:G8<>IF(ISBLANK(A2:F8),"",A2:F8))

becomes

(B2:G8<>{"",1,1,1,1,0;"",0,1,0,0,1;"",0,1,0,0,0;"",0,1,1,0,1;"",0,0,0,1,0;"",0,1,0,0,1;"",1,1,1,1,1})

becomes

({0, 1, 1, 1, 1, 0; 0, 0, 1, 0, 0, 1; 0, 0, 1, 0, 0, 0; 0, 0, 1, 1, 0, 1; 0, 0, 0, 0, 1, 0; 0, 0, 1, 0, 0, 1; 0, 1, 1, 1, 1, 1}<>{"",1,1,1,1,0;"",0,1,0,0,1;"",0,1,0,0,0;"",0,1,1,0,1;"",0,0,0,1,0;"",0,1,0,0,1;"",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 4 - Check if cell is 0 (zero)

This step filters groups of zeros, we don't want to count 1's.

B2:G8=0

becomes

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

and returns

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

Step 5 - Multiply arrays

(B2:G8<>IF(ISBLANK(A2:F8),"",A2:F8))*(B2:G8=0)

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}*{FALSE, FALSE, FALSE, FALSE, TRUE, TRUE; TRUE, FALSE, TRUE, TRUE, FALSE, TRUE; TRUE, FALSE, TRUE, TRUE, TRUE, FALSE; TRUE, FALSE, FALSE, TRUE, FALSE, FALSE; TRUE, TRUE, TRUE, FALSE, TRUE, FALSE; TRUE, FALSE, TRUE, TRUE, FALSE, TRUE; FALSE, FALSE, FALSE, FALSE, FALSE, TRUE}

and returns

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

Step 6 - Sum values

The SUM function adds the numbers in the array and returns a total.

SUM((B2:G8<>IF(ISBLANK(A2:F8),"",A2:F8))*(B2:G8=0))

becomes

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

and returns 14 in cell C11.

Download Excel file


Count-groups-of-repeated-values-per-row-1.xlsx

Related articles