Forum Discussion
COUNTIFS by Month Excluding Year
I'm attempting to do a COUNTIFS function based on a date range but want to exclude the year so that the date can be updated easily from one year to the next without having to redo all of the formulas. So for example, I want to count all of the cells occurring in January 2022, and then when next year rolls around I want to be able to flip the count to January 2023 (this is for a Goals tracker).
Is there a way to do that or am I out of luck? A copy of the table I'm working on is attached for a visual reference.
Being a committed Excel 365 user, I would employ something like
WorksheetFormula = MAP(goalDate, MonthlyTotalλ(date,goals)) MonthlyTotalλ = LAMBDA(mnth, goals, LAMBDA(gdate, SUM(IF(MONTH(date) = MONTH(gdate), goals)) ));... or would that constitute 'cruelty to Excel users'?
Presuming dates are in A2:A10. You could try:
=COUNT(IF(MONTH(A2:A10)=1,A2:A10))Press ctrl+shift+enter to define as an array if not using 365 or Excel 2021.
The reason COUNTIF/COUNTIFS does not work is both functions only accept ranges, not arrays. That's why this one won't calculate:
I think this is on the right path, but it's coming back with a crazy number as the result. If it helps, the dates listed are on a separate tab in a named column F. The result should be 0 based on the dates I have listed currently, but it's coming up as 1048573.
What am I missing?
It appears you have lots of 0s in that column. Try this and make sure the formula is not pointed at the entire column:
=COUNT(IF(TEXT(A2:A10,"mmm")="Jan",IF(A2:A10<>0,A2:A10)))
My guess from what you've written is that the Pivot Table would be a far better way to accomplish the results. There are all kinds of resources on-line that can get you started if you've never used the Pivot Table.
- Here, for example, are some YouTube videos.
- And here is a website that on this page speaks of Pivot Tables, but also has lots of other useful resources.
- I don't think a pivot would work in this case, would it? I need to be able to manually enter the Goal total, and then the difference between the goal and actual totals will update automatically. A pivot could definitely count the actual total for my columns but it can't adjust the Goals and Remaining number, can it?
You raise a good question. Let me back up a bit. What you have shown is the end report (minus those monthly counts); what you've NOT shown is the raw data from which those monthly counts will be taken. Could you give us a glimpse of how that data is arrayed?
There may be yet another way to accomplish this, using COUNT or COUNTA and FILTER.
And there may be other ways still. [One of the delightful, if sometimes confusing, things about Excel is that there are often multiple ways to get from A to B.]
