COUNTIF year (when year returned from a date field)

D.wilson
D.wilson ✭✭
in Formulas and Functions

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?

· Share on FacebookShare on Twitter

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

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

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

    · Share on FacebookShare on Twitter
  • D.wilson
    D.wilson ✭✭

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

    · Share on FacebookShare on Twitter
  • Stefan
    Stefan ✭✭✭✭✭✭
    edited 02/23/23

    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

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

    · Share on FacebookShare on Twitter
  • D.wilson
    D.wilson ✭✭
    edited 02/23/23

    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

    · Share on FacebookShare on Twitter
  • Ipshita
    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

    Ipshita Mukherjee

    · Share on FacebookShare on Twitter
  • Leibel S
    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

    · Share on FacebookShare on Twitter
  • D.wilson
    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.

    · Share on FacebookShare on Twitter
  • Ipshita
    Ipshita ✭✭✭✭✭✭

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

    Cheers!

    Ipshita

    Ipshita Mukherjee

    · Share on FacebookShare on Twitter
  • Stefan
    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

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

    · Share on FacebookShare on Twitter
  • Leibel S
    Leibel S ✭✭✭✭✭✭

    Can you post some snapshots?

    Something seems off

    · Share on FacebookShare on Twitter
  • D.wilson
    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!

    · Share on FacebookShare on Twitter
  • Stefan
    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

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    edited 02/23/23

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!