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!

Answers

  • Eric Law
    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
    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
    Eric Law ✭✭✭✭✭✭
    edited 03/21/23

    @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
    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
    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
    aecross ✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!