To count by month has nothing different than How to COUNT values between two dates. However, this article shows you a more dynamic and specialized way with EOMONTH function that you don't need to guess how many days in a month to count month.
If you only want to calculate the number of days in a month, please refer to this article: How to find number of days in month
Syntax
=COUNTIFS(
date range,
">=" & first day of month,
date range,
"<=" & EOMONTH(
first day of month,
0
)
)
Steps
- Start with =COUNTIFS(
- Continue with first criteria range – criteria pair with date range and 1st day of month $B$3:$B$12,">="&$D3,
- Enter second criteria range – criteria pair with date range and EOMONTH function $B$3:$B$12,"<="&EOMONTH($D3,0)
- Type ) to close COUNTIFS function and press Enter to complete the formula
How
First of all, the COUNTIFS function counts values that meet single or multiple criteria. Ability to use criteria with logical operators like greater than or equal (>=) and less than or equal (<=) provides the way of counting values between specific values.
To filter dates in a month, we need dates for the first and last days of that specific month. Although the first day of the month is easy to guess. But displaying a particular month like 11/1/2018 is not a preferred approach. Thanks to formatting options in Excel, we can display a full date as month name only. Adding custom format "mmmm" to a date displays it as a long month name without changing its value. To apply a custom format:
- Select the cell to be formatted and press Ctrl+1 to open the Format Cells dialog. An alternative way to do is by right-clicking the cell and then going to Format Cells > Number Tab.
- Under Category, select Custom.
- Type in the format code into the Type
- Finally, click OK to save your changes.
For detailed information on Number Formatting please visit: Number Formatting in Excel – All You Need to Know.
After adding the first days of months, under a formatting of month names, it is time to enter criteria range-criteria pairs. The first pair is easy: date range and the date of the first day of the month.
$B$3:$B$12,">="&$D3,
Next criteria range-criteria pair is the last of the month. We use the EOMONTH function that returns the date for the last day of a month. The EOMONTH gets date and month arguments. The date is the same date we use for first criteria and month argument gets 0 to point exact date in the first argument.
$B$3:$B$12,"<="&EOMONTH($D3,0)
Now that both criteria range-criteria pairs are set, it is time to use them in the COUNTIFS function to count month.
=COUNTIFS($B$3:$B$12,">="&$D3,$B$3:$B$12,"<="&EOMONTH($D3,0))
