List months and totals

[jaolvera]

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

[#2]

@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))

[#3]

Hi @jaolvera 

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))), "")

[#4]

@jaolvera 

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.

[#5]

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"

[#6]

@Harun24HR 

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

[#7]

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

[#8]

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

[#9]

@Harun24HR '

Yes I have updloaded the document

[#10]

referencing a column.

[#11]

were you able to access the document sample?