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?
Best Answer
-
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())
Answers
-
@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.
-
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())
-
Paul Newcome I tried your formula and it worked perfectly. Thanks so much :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!