CountIF won't recognise the result of a formula

I have a column that contains a column formula driven from a date.

GRID COLUMNS

Detection Date = DD/MM/YY

Year = formula: =20 + RIGHT([Detection Date]@row, 2). Result = 2023

Sheet summary

This Year = formula: =20 + RIGHT(TODAY(), 2). Result today = 2023

Breach This Year = formula: =COUNTIF(Year:Year, [This Year]#). Result = 0 (should count 113 instances of 2023).

The COUNTIF formula simply will not recognise the 2023 in the Year column. Even if i change the formula to state =COUNTIF(Year:Year, 2023) or put 2023 in "", the result is always the same as 0.

How can I update my COUNTIF to recognise the value of 2023 in the Year column?

Find more posts tagged with

Accepted answers

Paul Newcome

If you are using a date type column, you can just use the YEAR function directly within the COUNTIF and not worry about the helper column with the year in it at all.

=COUNTIFS([Detection Date]:[Detection Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY())

All comments

TVang

@Kate Rhodes, place your Year formula in VALUE(): VALUE(20 + RIGHT([Detection Date]@row, 2))

It's not needed but, for consistency, you can do the same with your This Year formula.

Paul Newcome

If you are using a date type column, you can just use the YEAR function directly within the COUNTIF and not worry about the helper column with the year in it at all.

=COUNTIFS([Detection Date]:[Detection Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY())

Kate Rhodes

Paul Newcome I tried your formula and it worked perfectly. Thanks so much :)

Paul Newcome

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