## Count groups of each value

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

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

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 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 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 a filtered table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]

Count overlapping days in multiple date ranges

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

Count unique distinct text values in a range

Array formula in D14: =SUM(IF(FREQUENCY(COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")), COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")))>0, 1, 0)) + CTRL + SHIFT + ENTER […]

Count overlapping days in multiple date ranges, part 2

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

### One Response to “Count groups 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!