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'?

Find more posts tagged with

Formulas

Support

functionality

Accepted answers

Genevieve P.

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

All comments

Paul Newcome

You have an extra opening square bracket just before the first column name that needs removed.

medhapar

Thank You Paul. But even after removing that square bracket the formula shows - #UNPARCEABLE.

Any other thoughts?

Genevieve P.

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

Paul Newcome

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

medhapar

@Genevieve P. That worked! awesome. Thank you for your help.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.