## Count cell blocks in excel

**Question: **

Sam asks:

Is there a formula that can count blocks

For eg in your picture (see picture above) if the green blocks had the letter G and the Red blocks had the letter R and I had to return 4 as the answer -3G + 1R

Is this possible through a formula?

*You can find the question here:
Count overlapping dates in excel*

**Answer:**

I have added some cell blocks to complicate things.

**Formula in B16:**

### Explaining formula in cell B16

=SUMPRODUCT(--($B$3:$AE$3<>$C$3:$AF$3), --($C$3:$AF$3<>""))+($B$3<>"")

**Step 1 - Check if next cell is not equal to current cell**

=SUMPRODUCT(**--($B$3:$AE$3<>$C$3:$AF$3)**, --($C$3:$AF$3<>""))+($B$3<>"")

--($B$3:$AE$3<>$C$3:$AF$3)

becomes

--({"G", "G", 0, "G", "G", "G", "R", "R", "R", "R", "R", "R", "R", "R", "R", "G", "G", "G", "G", "G", "G", "G", 0, 0, "G", "G", "G", "G", 0, "G"}<>{"G", 0, "G", "G", "G", "R", "R", "R", "R", "R", "R", "R", "R", "R", "G", "G", "G", "G", "G", "G", "G", 0, 0, "G", "G", "G", "G", 0, "G", "G"}

becomes

--({FALSE, TRUE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE})

and returns

{0, 1, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 1, 1, 0}

**Step 2 - Check if cells are empty**

=SUMPRODUCT(--($B$3:$AE$3<>$C$3:$AF$3), **--($C$3:$AF$3<>"")**)+($B$3<>"")

--($C$3:$AF$3<>"")

becomes

--($C$3:$AF$3<>"")

becomes

--({TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE})

and returns

{1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 1, 1, 1, 0, 1, 1}

**Step 3 - Check if first cell is not empty**

=SUMPRODUCT(--($B$3:$AE$3<>$C$3:$AF$3), --($C$3:$AF$3<>""))+**($B$3<>""**)

($B$3<>"")

returns TRUE

**Step 4 - All together**

=SUMPRODUCT(--($B$3:$AE$3<>$C$3:$AF$3), --($C$3:$AF$3<>""))+($B$3<>"")

becomes

=SUMPRODUCT({0, 1, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 1, 1, 0}, --($C$3:$AF$3<>""))+($B$3<>"")

becomes

=SUMPRODUCT({0, 1, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 1, 1, 0}, {1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 1, 1, 1, 0, 1, 1})+($B$3<>"")

becomes

=SUMPRODUCT({0, 1, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 1, 1, 0}, {1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 1, 1, 1, 0, 1, 1})+TRUE)

becomes

=5+TRUE

and returns 6.

**Download excel sample file for this tutorial.**

count blocks.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**SUMPRODUCT(**array1, array2, **)**

Returns the sum of the products of the corresponding ranges or arrays

### Category: Count values

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. Tip! I highly […]Comments(93) Filed in category: Count unique distinct values, Count values, Excel

Count unique distinct values in a column

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: First, let me explain […]Comments(26) Filed in category: Count unique distinct values, Count values, Excel

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 […]Comments(26) Filed in category: Conditional formatting, Count values, Excel

Count number of times a string exist in multiple cells using excel formula

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]Comments(19) Filed in category: Count text values, Count values, Excel

Excel: List intervals between two values

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 […]Comments(16) Filed in category: Count values, Excel, Range

### Category: Dates

Create a date range using excel formula

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 […]Comments(142) Filed in category: Dates, Excel

Lookup a value and find max date

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]Comments(83) Filed in category: Dates, Excel

Formula for matching a date within a date range in excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]Comments(48) Filed in category: Dates, Excel

Finding the nearest date in a range of dates using excel formula

Array formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) Recommended articles: How to enter an array formula Select cell E3 Type […]Comments(42) Filed in category: Dates, Excel

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]Comments(31) Filed in category: Excel, Overlapping

### 2 Responses to “Count cell blocks in excel”

### Leave a Reply

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

<code>your formula</code>

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

Oscar..Thanks for this brilliant formula.

One more question for the Calendar that you have set up above can we have a excel formula which will give us a below table

StarWk EndWk Name

1 2 G

4 6 G

7 15 R ... and so on

Sam,

Read this post: https://www.get-digital-help.com/2010/08/29/extract-dates-from-a-cell-block-schedule-in-excel/