# List months and totals

Brass Contributor

# List months and totals

Hello, So I want to create a formula that would reference a Column in another worksheet, the referenced Column will have a list of dates and I would like to pull out Month and year and count the number of times each is referenced in that range.

i.e

Nov 2023-3

Dec 2023-6

January 2024-5

I am not sure if that can all be referenced in one cell or if it needs 2 or 3.  please let me know

10 Replies

# Re: List months and totals

@jaolvera With Microsoft-365 you can achieve that.

``````=LET(
x,UNIQUE(EOMONTH(+A2:A8,0)),
y,COUNTIFS(A2:A8,">="&EOMONTH(x,-1)+1,A2:A8,"<="&x),
HSTACK(TEXT(x,"mmm yyyy"),y))``````

# Re: List months and totals

I think you can use FREQUENCY combine with UNIQUE to solve this.

FREQUENCY has two parameters; first is data range and second is a "bin" to group the count. UNIQUE give us a unique list of

Example lets say your dates are in column A21 to A27. Below formula gets the unique values in range A21 to A27 and the frequency of the number of count.

``=IFNA( TEXT(SORT(UNIQUE(A21:A27)), "mmm yy") & " - " & FREQUENCY(A21:A27, SORT(UNIQUE(A21:A27))), "")``

# Re: List months and totals

Yet another option: a pivot table with the date field (column) in both the Rows and Values areas.

Group the row field by Years and Months.

# Re: List months and totals

this worked! is there anyway that we can account for blanks in this formula, since the data rance can vary, and this formula will be in place prior to data being entered, I want to make sure it wont calculate the "blank " cell as "jan 1900"

# Re: List months and totals

is there a way to count for blanks so "jan 1900" doesnt get counted? also not sure what the first line is representing? also the formula will be calculated as data is being inputted so there will be blank cells, so the range can vary.

not sure if there is a way to account for that

# Re: List months and totals

@hansvogelaar are you maybe able to assist me with correcting this ?
=LET(
x,UNIQUE(EOMONTH(+A2:A8,0)),
y,COUNTIFS(A2:A8,">="&EOMONTH(x,-1)+1,A2:A8,"<="&x),
HSTACK(TEXT(x,"mmm yyyy"),y))
this is the formula I used.

# Re: List months and totals

Yes, we can exclude blank cells. Can you share a sample workbook? Are the blank cells inside data or you referencing full column?

# Re: List months and totals

Yes I have updloaded the document

# Re: List months and totals

referencing a column.

# Re: List months and totals

were you able to access the document sample?