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 Au...
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
- I think for a pivot table you might add a column to your data and use Text(A1, "mmm yy") and accumulate on this column.
Or, if you want a formula, you could try:
=Sumproduct(--(TEXT(LI:LI,"mmm yyyy")="Jul 2021"))
To add more conditional tests:
=Sumproduct(--(TEXT(LI:LI,"mmm yyyy")="Jul 2021"), --(range <=> condition))- I think this works great but was wondering if you could help me with the syntax when referencing to another workbook?
