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

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.

Get the Excel file

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

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Find empty hours in a weekly schedule

The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

Populate drop down list with unique distinct values sorted from A to Z

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Search for a sequence of cells based on wildcard search

This article demonstrates array formulas that perform a wildcard search based on a sequence of values. The formulas return the […]

Time sheet for work

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]

Unique distinct list from a column sorted A to Z

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]

Count unique distinct values

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