## Count unique distinct months

The formula in cell D18 counts unique distinct months in cell range B3:B16.

Formula in D18:

### Explaining formula in cell D18

#### Step 1 - Convert dates

The DATE function changes each date to the first in the given month and year.

DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1)

becomes

DATE(YEAR({40059; 40228; 39946; 39919; 39891; 39921; 40106; 40243; 40144; 40202; 40179; 39880; 40171; 40019}), MONTH({40059; 40228; 39946; 39919; 39891; 39921; 40106; 40243; 40144; 40202; 40179; 39880; 40171; 40019}), 1)

becomes

DATE({2009; 2010; 2009; 2009; 2009; 2009; 2009; 2010; 2009; 2010; 2010; 2009; 2009; 2009}, {9; 2; 5; 4; 3; 4; 10; 3; 11; 1; 1; 3; 12; 7}, 1)

and returns

{40057; 40210; 39934; 39904; 39873; 39904; 40087; 40238; 40118; 40179; 40179; 39873; 40148; 39995}

#### Step 2 - Count frequency of each number

The FREQUENCY function returns an array of numbers representing how many times the number occurs in the list.

FREQUENCY(DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1),DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1))

becomes

FREQUENCY({40057; 40210; 39934; 39904; 39873; 39904; 40087; 40238; 40118; 40179; 40179; 39873; 40148; 39995},{40057; 40210; 39934; 39904; 39873; 39904; 40087; 40238; 40118; 40179; 40179; 39873; 40148; 39995})

and returns

{1;1;1;2;2;0;1;1;1;2;0;0;1;1;0}.

#### Step 3 - Check if value is larger than 0 (zero)

We know a value is unique distinct if it is larger than 0 (zero).

(FREQUENCY(DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1),DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1))>0

becomes

{1;1;1;2;2;0;1;1;1;2;0;0;1;1;0}>0

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE}

#### Step 4 - Convert boolean values

We must first convert the boolean values in order to sum the values, multiply with 1 to create their numerical equivalents.

(FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1))>0)*1

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE}*1

and returns

{1;1;1;1;1;0;1;1;1;1;0;0;1;1;0}.

#### Step 5 - Sum values

Use the SUMPRODUCT function to add the numbers and return a total.

SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1),DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1))>0)*1)

becomes

SUMPRODUCT({1;1;1;1;1;0;1;1;1;1;0;0;1;1;0})

and returns 11 in cell D18.

### Get Excel *.xlsx

### Count unique distinct 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 […]

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

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

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

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

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

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