Require Help with COUNTIF Formula on Smartsheets

medhapar

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

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

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.