# CountIF between 2 dates and Notblank cells

Options
edited 12/09/19

Hello community! First post here

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

Example:

March: 13

April: 10

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:

Returns: #UNPARSEABLE

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

Where is my mistake?

Thanks,

Vanzin

Tags:

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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!