COUNTIFS and YEAR(TODAY()) not working

Hi,

I'm trying to set up a formula, but the result comes up as #INVALID DATA TYPE. 

I'm at a loss of what's wrong with my formula. I'm using a very similar one in a different sheet and it's working.


This is the formula that's not working: 

=COUNTIFS(City:City, City@row, [Event Year]:[Event Year], YEAR(TODAY()))


And this one is working:

=COUNTIFS({MCE Meeting Intake Form Range 2}, [Chosen Countries]@row, {MCE Meeting Intake Form Range 5}, YEAR(TODAY()))

where {MCE Meeting Intake Form Range 2} is Country:Country

and {MCE Meeting Intake Form Range 5} is [Event Year]:[Event Year]


Does anyone see my error?


Thanks,

Gudrun

Best Answer

  • Gudrun Bornschein
    Answer ✓

    Hi @Andrée Starå ,

    Sorry for disappearing for a while. I was working on a copy of the sheet for you and suddenly the formula was working in the copy. Then I got super busy with other things and couldn't look further into it till now.

    Anyway, I found the issue: The Event Year column is also a formula, connected to the start date of my events. Some events don't have a start date defined yet, hence are empty and the related Event Year cell was returning #INVALID DATA TYPE, causing my other formula referencing that column to fail.

    I fixed the issue by changing the Event Year column formula from

    =YEAR([Start Date]@row)

    to

    =IFERROR(YEAR([Start Date]@row), "")


    Thanks again for all your help!

    Best,

    Gudrun

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!