## Count the number of groupings of each value

*Article updated on April 21, 2012*

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:**

**Array formula in cell J2:**

**How to create an array formula**

- Select cell I2
- Paste formula
- Press and hold Ctrl + Shift
- 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 how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]Count how many numbers between a specific value occurring multiple times

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]### One Response to “Count the number of groupings of each value”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

That works!! Great Stuff!

Thanks Oscar!