IFERROR / COUNTIFS Formula

aecross

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!

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.