CountIF between 2 dates and Notblank cells
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
Comments
-
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:
- 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.
- 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 - 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:
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 142 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!