How to count quantity of date entries within a month time frame?

Jef Forward

 

Hello All - 

 

I have 10 years of data that I would like to produce historical infomration, and eventually trailing charts.

 

I want to produce a chart that will tell me how many leads we received on a per month, per year basis.

 

I have shared a screen shot, and example of what I am trying to do.  I keep getting a "zero" for a result.  The formula I am trying to use is 

=COUNTIFS($[Received Lead Date]:$[Received Lead Date], [Column3]5, $[Received Lead Date]:$[Received Lead Date], "2015")

 

Is there a different way to do this?  Any help woul be appreciated, Thanks

 

Jessica Harris

You need to create 2 more columns for your data. In one column, you need to have the formula: =MONTH([Received Lead Date]), and the other =YEAR([Received Lead Date]).

 

Here is an example:

 

 

[Month]x = [Received Lead Date]x

[Year]x = [Received Lead Date]x

Summary Month is the number version of the months (January = 1, etc)

Each summary column is =COUNTIFS($Month:$Month, $[Summary Month]x, $Year:$Year, $[20xx]$1)

 

So January 2014 = COUNTIFS($Month:$Month, $[Summary Month]2, $Year:$Year, $[2014]$1)

Joel Johnson

Jessica is correct.  The countif() functions need to have a numeric value to work with, they don't like dates.  So pulling the Month, and Year data out of your dates doesthe trick.