Forum Discussion
COUNT IF FORMULA for month/year only
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:
- Jul 2021
- Aug 2021
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!
12 Replies
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)
- To use year along with month, in (K) use:
TEXT(DATE(YEAR(G8),MONTH(G8),),"mmm"&" "&"yyyy")
Make sure to use 'Month 2021' in K5. Note Month should be 3 letters. make changes to the formulas if you want months to be shown differently.
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.
- hello
what about if i have in the same day 3 entries like 1-july 20 7:20
1-july-20 7:25
1-july-20 7:30
will it still work, as these are different dates for excel, it will show 3 items in the row label isnt?- Well, give it a try and see what happens.
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
- This counts dates right? What if I want to get count all July dates?
- if all july without specifying year, then we need to slightly modify the formula
- if you convert the range to pivot table & put the dates in rows & put again dates in values (select count there), so it will count how many month year u have
