CountIF between 2 dates and Notblank cells

edited 12/09/19 in Formulas and Functions

Hello community! First post here laugh

I need to count the number of cells in a separate column per month.


March: 13

April: 10


Ok, what I've already tried:

=COUNTIFS((Criado:Criado); @cell = DATE(2018; 3))

Returns: 0 counts

Expected: 20 counts

=COUNTIFS([Título da ideia]:[Título da ideia]; NOT(ISBLANK(@cell)))

Returns: 3 counts

Expected: 3 counts


Trying to join the two formulas:

=COUNTIFS((Criado:Criado); @cell = DATE(2018; 3)); [Título da ideia]:[Título da ideia]; NOT(ISBLANK(@cell))


Expected: 3 counts (3 results on March and 3 results not blanks cells)

Where is my mistake?






  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi Vanzin,

    It is a woeful tale of missing functions that prevents you from taking the easy path here.

    Your second formula looking for non-blank cells in the [Título da ideia] column is correct. As you may have suspected, it is your date formula that is producing the error. COUNTIF typically has issues with non-numeric (i.e. date) values. Normally, you'd use something like EOMONTH, EDATE or even SUMPRODUCT to do the job, but alas, those functions are not available in Smartsheet.

    You have two options here:

    1. As the time component of your time/date stamp is useless, you can usually bank on the fact that the date will be the 4th & 5th character of an 8 character DD/MM/YY string (unlike those crazy Americans that like to put the date first smiley). This means you can extract the month using this formula:

      =COUNTIFS(Date:Date, MID(@cell, 4, 2) = "04", Value:Value, NOT(ISBLANK(@cell)))

      The challenge here is twofold. You will not be able to leverage this information elsewhere (where you're looking for a month) unless you do the same thing and there is a potential for someone to swap things into the MM/DD/YY format.

    2. Alternatively, you can create another column and simply populate it with =MONTH(Criado1) and copy it all the way down, then matching the month via the following formula:

      =COUNTIFS(Month:Month, 4, Value:Value, NOT(ISBLANK(@cell)))

    Hope this helps.

    Kind regards,

    Chris McKay

    Down Under Smartsheet Support

  • vanzin

    Hi Chris! It worked!

    I modified the formula to also count per year:

    =COUNTIFS(Criado:Criado; MID(@cell; 4; 5) = "09/16"; [Título da ideia]:[Título da ideia]; NOT(ISBLANK(@cell)))



  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi Vanzin,

    That's great! It's a great feeling when you can adapt something to another use like you did with the year formula.

    All the best,

    Chris McKay

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!