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

Find more posts tagged with

Accepted answers

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)

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

Thanks again for all your help!

Best,

Gudrun

All comments

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å ,

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

=YEAR([Start Date]@row)

=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!

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.