Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
How to count quantity of date entries within a month time frame?
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
Comments
-
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)
-
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.
