count occurrences of a word within a date range like month or quarter


Keywords:excel 


Question: 

I have a sheet with data organized by date for an entire year. Column "a" would be the dates in chronological order and column "b" would have a name in each cell. Many of the names are repeated throughout the year.

What formula would I use to count how many times a particular name was listed in column b within a particular month, quarter or date range of my choice?

I would like to create a new sheet to show this data once I have the proper formula/s.

I am using excel 2016


1 Answer: 

OK, let's specify your inputs like this:

E1: name
E2: start date
E3: end date

Then to do the calculation:

=CountIf( B:B,E1, A:A,">="&E2, A:A,"<"&E3 )

So for 2016 you'd put 2016-01-01 in E2 and 2017-01-01 in E3.

(If you use "<=" and 2016-12-31 for the end of the year, you run a slight risk that a date+time combination for the last day of the year will be excluded. Better to always use lower<=x and x<upper to avoid this.)