Jan 25 2022 08:32 PM - edited Jan 25 2022 08:35 PM
Hi guys, I want to count rows based on the month year.
The column I want to count is in this format:
27/07/2021 0:00 |
but I want to count it by month/year only. eg:
I have this formula that I want to use (COUNTIFS because I have a couple more criterias to add)
=COUNTIFS(LI:LI, "Jul 2021") or 07/21 or whatever works!!
I've tried doing this:
=COUNTIFS(TEXT(LI:LI,"mmm yyyy"), "Jul 2021") but it doesn't work. Any help would be appreciated!
Jan 25 2022 08:44 PM
Jan 25 2022 09:04 PM
@alexesprtu please have a look, your dates are in this format date & time, so first it should be changed to only date, then you can use either countifs or pivot table
Jan 25 2022 09:19 PM
Jan 25 2022 09:28 PM
Jan 25 2022 09:33 PM
Jan 25 2022 09:47 PM
Jan 25 2022 09:52 PM
Jan 25 2022 10:07 PM
@alexesprtu As a variant to all previous answers:
If the date/time entries are real date/time values, i.e. not texts looking like date/time, you can use a pivot table as suggested earlier. But you don't need to have the first conversion step. Pivot tables handle real date/time values very well and allow you to group them by year and month, without the need for formulae.
In the attached file I created a list of date/time values covering 6 years and created a simple pivot table from it (the first one). Select one specific year to display the count for each month for that year or select "All" to display the count for all months for all years together. Then you can create other tables as well from the same data source as demonstrated in the file. Your imagination is the only limiting factor here.
Jan 25 2022 10:21 PM
The criteria is your input variables. You define the month and date you want to count.
The count row shows the count. Here are the formulas used:
COUNTIF(H8:H18,H4) in H5
COUNTIF(I8:I18,I4) in I5
COUNTIF(K8:K18,K4) in K5
COUNTIF(L8:L18,L4) in L5
Now for the columns 'Isolating Month' and 'Isolating Date':
MONTH(G8) for (H)
DAY(G8) for (I)
TEXT(DATE(,MONTH(G8),),"mmm") for (K)
(L) shares the same formula as (I)
Jan 25 2022 10:26 PM
Jan 25 2022 11:43 PM
Jan 25 2022 11:49 PM