Error with COUNTIFs and date formula

Hello!

Can someone please advise what am I doing wrong getting this error?

The formula used is as follows:

=COUNTIFS({ref sheet}, AND(MONTH(@cell) = 1, YEAR(@cell) = 2021))

The date column in the reference sheet is set as "Date" and the format is DD-Mon-YYYY (e.g. 19-Jan-2021). I've tried creating a default date column in my metrics sheet too, however, it made no change.

Any help is appreciated!

Find more posts tagged with

Formulas

Accepted answers

Bassam Khalil

Hi @Michaela Kamenska,

Hope you are fine, please try the following formula

=COUNTIFS({ref sheet}, IFERROR(YEAR(@cell), 0) = 2021, {ref sheet}, IFERROR(MONTH(@cell), 0) = 1)

All comments

Bassam Khalil

Hi @Michaela Kamenska,

Hope you are fine, please try the following formula

=COUNTIFS({ref sheet}, IFERROR(YEAR(@cell), 0) = 2021, {ref sheet}, IFERROR(MONTH(@cell), 0) = 1)

Michaela Kamenska

Hi @Bassam.M Khalil ,

Thank you for your response. I appreciate your solution, however, this is a dynamic sheet where many rows are added every day. Is there a solution where I can use the whole column as a ref?

Bassam Khalil

Hi @Michaela Kamenska

sorry i correct the formula for you please read my response again

Michaela Kamenska

Thank you very much, it works great!

Bassam Khalil

Happy to help you any time @Michaela Kamenska

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.