COUNTIF year (when year returned from a date field)

I am trying to count the number of rows containing the year 2023 in a column called [Application Year]. The [Application Year] column is returning a 4-digit year (using the YEAR[Date Column] function), where [Date Column] is pulling a date field from another sheet. I have tried a variety of formulas, but none are working, and I'm wondering if it's because it's not reading the 4-digit year as text or a date.

Here are some of the things I've tried:

=COUNTIF([Application Year]:[Application Year], 2023) - This returns a #NOMATCH error

=COUNTIF(CONTAINS("2023", [Application Year]:[Application Year])) - This returns a #INCORRECT ARGUMENT error

I've tried other things, but I have a feeling that whatever is wrong here is wrong with all of them.

Suggestions?

Find more posts tagged with

Accepted answers

All comments

Lucas Rayala

You need to use the "@cell" reference in your criteria:

=COUNTIF([Application Year]:[Application Year], CONTAINS("2023",@cell))

D.wilson

Hmmm... still returning a #NOMATCH error...

Stefan

Hi @D.wilson,

your second formula cannot work, the COUNTIF statement is not complete. Thus the incorrect argument error.

As your first formula looks ok, I tried it to be sure I don't forget anything. In my sheet your formula works as expected.

The cell where I use =COUNTIF([Application Year]:[Application Year], 2023) sits in a column of the type "text/numbers".

"Application Year" and "Date Column" are both date columns.

In "Date Column" I use =YEAR([Date Column]@row) ti grab the 4 digit year.

Is this also your setting?

Greetings

Stefan

D.wilson

Thanks @Stefan! So, when I try to use the =YEAR([Date Column]@row) formula in a date column, it gives me a #DATE EXPECTED error code...

Ideas?

Dahvi

Ipshita

@D.wilson your 'Year' Column must be a Text/Number column type, it would still return the year from a date column. Then you can use it in your other formula.

Cheers!

Ipshita

Leibel S

@D.wilson

Is there any errors in your [Application Year] column?

Sounds like one of the cells there have #NOMATCH Error.

Most probably because the date column it is referencing has a #NOMATCH Error

D.wilson

@Ipshita Unfortunately, when I make the [Application Year] column a text/number column, my COUNTIF calculation isn't working...

@Leibel S There are no errors showing in my [Application Year] column.

For some reason, it isn't recognizing the YEAR output when it's trying to run the COUNTIF.

Ipshita

@D.wilson - I suggest you raise this issue with Smartsheet support as there might be a technical error in here.

Cheers!

Ipshita

Stefan

https://community.smartsheet.com/discussion/comment/363876#Comment_363876

@D.wilson

Sorry typo error on my side!

Date Column = Date type

Application Year = Text/Number type

In addition Leibel S may also have a point here. You mentioned that you get the date from another sheet. If the "Date Column" can have empty cells due to empty cells in the source sheet, than the YEAR function shows an error.

To make your formula resistent against empty cells use IFERROR

https://help.smartsheet.com/function/iferror

So: =IFERROR(YEAR([Date Column]@row), "")

Hope this helps

Stefan

Leibel S

Can you post some snapshots?

Something seems off

D.wilson

Thanks everyone. Yes, I think something is off here. Even basic COUNTIF formulas don't seem to be working right. I'll contact support. THANK YOU!

Stefan

@D.wilson, before contacting support, did you try to use another browser and thinking about it, did yo log out, clear caches and log in again?

Greetings

Stefan

Genevieve P.

Hi @D.wilson

I agree that screen captures would be helpful and a list of columns / column types!

One other troubleshooting note:

Is it possible that your column [Application Year] houses a formula error? I would check to make sure none of the cells are returning an error in that column, it would bubble up to error in any other formulas looking at it.

You can wrap the [Application Year] formula in an IFERROR if that's the case!

=IFERROR(formula, "")

Cheers,

Genevieve

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.