## Count cell blocks

*Article last updated on March 13, 2018*

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

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

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

### 2 Responses to “Count cell blocks”

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

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/