INVALID DATA Type when using countifs formula referencing from another sheet

Hi,

I am computing for weekly counts of leads generated for my report and I am using countifs formula and refer to the working sheet where my data is located.

I am pretty sure that I am referencing in the correct Date Column and it's data type is also a Date. But the formula is prompting an "Invalid data type error". When I try to use the formula to the working sheet itself and select a range of cells, it is working. Please see the screenshot below.

The first image shows my formula referencing to the working sheet, but when I hit enter it prompts "INVALID DATA TYPE". The second image is the my reference column. The third image is when I tried the formula to the working sheet itself and it is working.

Please help on how can I fixed the "INVALID DATA TYPE" error.

Reference in the same sheet formula is working.JPG

Find more posts tagged with

Formulas

Accepted answers

All comments

Andrée Starå

Hi @Benjo09

Try this.

=COUNTIFS({Fresh Leads Master Range 1}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))

Did that work?

I hope that helps!

Be safe and have a fantastic week!

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!

Genevieve P.

Hi @Benjo09

The error occurs when it's looking at the entire column, which includes blank cells that aren't dates. (You'll notice that for the formula that's in the sheet itself, you have a specific range listed - from rows 1-12, which is why it doesn't error).

To fix this, we can just wrap an IFERROR statement around your WEEKNUMBER(@cell) function to say that if there's an error there (so, if the cell is not a date) just read it as "0" instead.

Try this:

=COUNTIFS({Fresh Leads Master Range 1}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))

Let me know if this works for you!

Cheers,

Genevieve

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.