Count all records from certain month in excel

I have a sheet of trainings in excel that are organized by date and I am trying to do a monthly statistics report. So, I want to count, through an excel formula, the number of trainings that were done in the month that is specified in the header of the statistics column. The header has the month in number format and the dates in the training sheet are in 01/23/2015 format. For example, if the header of the stat column was "1" I would want to count all the trainings that were done in January 2015. Any help with this issue would be greatly appreciated!

回答1:

If the dates of the exercises are in format mm/dd/yyyy I would suggest making a new column (in column B) for the month and a new column (in column C) for the year

The formula for extracting months from a column is

=TEXT(A1,"mmmm")

The formula for extracting years is

=YEAR(A1)

Add these formulas to columns B and C and now you can use the filters in excel to show only training exercises from the year and month you want. I think the filters are the best way to view the exercises you want and it is easy to get counts from these looking at the number of rows in the filter table.

However, if you want to show the number of exercises that occurred in a certain month/year you can write an if statement that will give a value of 1 if your criteria is matched and a value of 0 if the criteria is not matched. This will look something like this:

=IF(B1="January",1,0)

This will check if the month in column B is "January", then return the value 1 if it is "January". If it is not January, it will return 0.

To see the number of times a year appears use:

=IF(C1=2015,1,0)

This will check if the year in column C is 2015, than return the value 1. If it is not January, return 0.

Add these formulas to columns D and E.

Then sum the values in this column

=SUM(D1:D1000)

for month, and

=SUM(E1:E1000)

for year, and you will get the number of reports that matches your criteria. This is one way you can use Excel formulas to obtain the number of exercises in a certain month or year. If you have more than 1000 reports just change the number to 10,000 or more.

Hope this helps!

回答2:

With the "header of the stat column" as A1 and the training dates in A2:A999 you could use,

=countifs(a2:index(a:a, match(1e99, a:a)), ">="&date(2015, a1, 1), a2:index(a:a, match(1e99, a:a)), "<"&edate(date(2015, a1, 1), 1))