IFERROR / COUNTIFS Formula

Hi there -

I am trying to count stores that are assigned to Rosa and are in January of 2023. I'm able to count just Rosa's stores, but I receive an #UNPARSEABLE error when I add in the dates. I'm not quite sure where to go from here...

=IFERROR(COUNTIFS({MASTER TRACKING SHEET Range 5}, @cell = "Rosa", {MASTER TRACKING SHEET Range 2}, AND((MONTH(@cell), 0) = 1, (YEAR(@cell), 0) = 2023)))

I appreciate all help!

Find more posts tagged with

Accepted answers

All comments

Eric Law

Your formula should be

=COUNTIFS({MASTER TRACKING SHEET Range 5}, "Rosa", {MASTER TRACKING SHEET Range 2}, IFERROR(MONTH(@cell) = 1, " "), {MASTER TRACKING SHEET Range 2}, IFERROR(YEAR(@cell) = 2023, " "))

aecross

@Eric Law - thank you for your help!

When i enter this formula, i am now getting #INVALID DATA TYPE. They are referencing entire columns.

=COUNTIFS({MASTER TRACKING SHEET Range 5}, "Rosa", {MASTER TRACKING SHEET Range 2}, IFERROR(MONTH(@cell) = 1, ""), {MASTER TRACKING SHEET Range 2}, IFERROR(YEAR(@cell) = 2023, ""))

Eric Law

@aecross

Where are you populating this? Can you try putting this in the summary tab and change your references to columns? Here is a snapshot of it.

I figured it out, you probably have blank cells in the date column which makes the #INVALID DATA TYPE

aecross

@Eric Law - correct, i do have blank cells in the date column. But i am referencing a different sheet with this formula.

I'm a bit confused on how to turn my existing formula =COUNTIFS({MASTER TRACKING SHEET Range 5}, "Rosa", {MASTER TRACKING SHEET Range 2}, IFERROR(MONTH(@cell) = 1, ""), {MASTER TRACKING SHEET Range 2}, IFERROR(YEAR(@cell) = 2023, "")) into the new one you are suggesting.

Eric Law

@aecross Ok, so we will need to rearrange your formula a bit and add 1 more section and it should work.

=COUNTIFS({MASTER TRACKING SHEET Range 2}, NOT(ISBLANK(@cell)), {MASTER TRACKING SHEET Range 2}, IFERROR(MONTH(@cell) = 1, ""), {MASTER TRACKING SHEET Range 2}, IFERROR(YEAR(@cell) = 2023, ""),{MASTER TRACKING SHEET Range 5}, "Rosa")

This will remove all your blank date cells first, so it won't give you the INVALID DATA TYPE error.

aecross

@Eric Law - that worked! Thank you so much! Really appreciate all of your help.

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.