COUNTIFS and YEAR(TODAY()) not working

Gudrun Bornschein

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

Gudrun Bornschein

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

Mark Cronk

Try:

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

Mark

Gudrun Bornschein

Hi Mark,

Thank you for your comment! Unfortunately that didn't solve it.

Andrée Starå

Hi @Gudrun Bornschein

Should the formula reference another sheet or only the same sheet it's in?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Gudrun Bornschein

Hi Andrée,

The formula that is not working is only referencing within the same sheet. (The one that is working is referencing to a different sheet).

Thanks,

Gudrun

Andrée Starå

@Gudrun Bornschein

Happy to help!

Try something like this.

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

Did that work?

Gudrun Bornschein

@Andrée Starå

Thanks again, but still no :( Copying my formula below in case you see an error in it.

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

Andrée Starå

@Gudrun Bornschein

Try pasting my version instead of typing it in. I can see an error in yours.

You used @row. It should be @cell.

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

Did it work?

Gudrun Bornschein

@Andrée Starå

Thanks again for being so patient with me. I tried the following two versions, but neither worked.

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

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

Andrée Starå

@Gudrun Bornschein

What type of columns is it?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Gudrun Bornschein

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

Andrée Starå

@Gudrun Bornschein

No worries! Glad you got it working!

You're more than welcome!