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?

Jef Forward
Jef Forward ✭✭✭✭
edited 12/09/19 in Archived 2015 Posts

 

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

 

counting entries with in a month time frame.png

Comments

  • Jessica Harris
    edited 12/07/15

    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)

    Smartsheet_Help_Screenshot.PNG

  • 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.

This discussion has been closed.