Author: Oscar Cronquist Article last updated on March 12, 2018

Question: How do I count the number of unique months in excel?



Array formula in C17:

=SUM(IF(MATCH(DATE(YEAR(List), MONTH(List), 1), DATE(YEAR(List), MONTH(List), 1), 0)=ROW(List)-MIN(ROW(List))+1, 1, 0)) + CTRL + SHIFT + ENTER

Named ranges
List (A2:A15)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change the named range.

Download excel sample file for this tutorial.
Count unique distinct months.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code

MONTH(serial_number) returns the month, a number from 1 (January) to 12 (December)

YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999

Adds all the numbers in a range of cells

Returns the smallest number in a set of values. Ignores logical values and text

This blog article is one out of twelve articles on the same subject "count unique and unique distinct".