Require Help with COUNTIF Formula on Smartsheets
Hello everyone!
I am trying to count the number of records for the current year(2021) from 'System Generated' date column -(Request Date )with formula - =COUNTIF(Request date: Request date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) in the 'Sheet Summary' section. This is not working - says #UNPARSEABLE
Alternatively I have written a formula in 'sheet summary' to calculate current year with formula =YEAR(TODAY()) and added a new column in the smartsheet to give a true or false for current year with formula =IF(YEAR[Request Date]@1=[Current Year]#,"true","false").The true and false IF statement is also not working on the new column.
Can you suggest me a better way of counting the number of records for the current year from system generated date column in 'Sheet Summary'?
Best Answer
-
Hi @medhapar
For your sheet summary formula, you need one set of square brackets around the column names. Try this:
=COUNTIF([Request Date]:[Request Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
Then for your formula in the sheet, you need (these) around the cell reference. You are also missing the @row portion. Try this:
=IF(YEAR([Request Date]@row) = [Current Year]#, "true", "false")
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
You have an extra opening square bracket just before the first column name that needs removed.
-
Thank You Paul. But even after removing that square bracket the formula shows - #UNPARCEABLE.
Any other thoughts?
-
Hi @medhapar
For your sheet summary formula, you need one set of square brackets around the column names. Try this:
=COUNTIF([Request Date]:[Request Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
Then for your formula in the sheet, you need (these) around the cell reference. You are also missing the @row portion. Try this:
=IF(YEAR([Request Date]@row) = [Current Year]#, "true", "false")
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Thank you.
@medhapar Genevieve got it all. The square bracket at the beginning. "@cell" inside of the YEAR function. "@row" in the second formula.
-
@Genevieve P. That worked! awesome. Thank you for your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!