## Count the number of groupings of each value

*Article last 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 unique distinct values that meet multiple criteria

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

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

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

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 […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Count cells between a given value

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 […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Count unique distinct values in two columns

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]

Count specific text string in a cell

Question: How do I count how many times a text string occurs in a cell in excel? Answer: Formula in […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

### One Response to “Count the number of groupings of each value”

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

That works!! Great Stuff!

Thanks Oscar!