## Count groups of repeated values per row

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:

Array formula in cell C11:

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

- Copy formula above.
- Select cell C11 with mouse.
- Paste formula (keyboard shortcut CTRL + v)
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once
- 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.

### Count values category

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]

The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### One Response to “Count groups of repeated values per row”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

That works!! Great Stuff!

Thanks Oscar!