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?
- I would let excel do the work, click in the formula bar to go into 'edit' mode, then use the mouse to select the range in your other workbook and excel should handle the syntax.
