CountIF between 2 dates and Notblank cells

vanzin

Hello community! First post here 



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

Example:

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))

Returns: #UNPARSEABLE

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

Where is my mistake?

Thanks,

Vanzin

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

Thanks!!!

Vanzin

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