如何计算日期条目的数量在一个星期一吗th 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.