List months and totals

Question

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

harun24hr · Answer

jaolvera&nbsp;With Microsoft-365 you can achieve that.&nbsp;=LET(
x,UNIQUE(EOMONTH(+A2:A8,0)),
y,COUNTIFS(A2:A8,"&gt;="&amp;EOMONTH(x,-1)+1,A2:A8,"&lt;="&amp;x),
HSTACK(TEXT(x,"mmm yyyy"),y))&nbsp;&nbsp;

sookoon · Answer

Hi&nbsp;jaolvera&nbsp;I think you can use FREQUENCY combine with UNIQUE to solve this.&nbsp;FREQUENCY has two parameters; first is data range and second is a "bin" to group the count. UNIQUE give us a unique list of&nbsp;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.&nbsp;&nbsp;=IFNA( TEXT(SORT(UNIQUE(A21:A27)), "mmm yy") &amp; " - " &amp; FREQUENCY(A21:A27, SORT(UNIQUE(A21:A27))), "")&nbsp;&nbsp;

hansvogelaar · Answer

jaolvera&nbsp;
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.
&nbsp;

jaolvera · Answer

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"

jaolvera · Answer

Harun24HR&nbsp;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

Forum Discussion

List months and totals

10 Replies

Resources