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

### 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 explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

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

### Functions in this article

More than 1300 Excel formulas

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