## Count groups in calendar

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

The image above shows the groups in row 3, the formula in cell B16 counts the number of groups.

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

**Functions in this article:**

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

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

### Count values category

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

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

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

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]

The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]

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

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]

### Dates category

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

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

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

This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

This article explains how to find the smallest and largest value using two conditions. In this case they are date […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

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

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

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

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]

This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 2 Responses to “Count groups in calendar”

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

Paste image link to your comment.

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