Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    IF & ISBLANK with two Criteria in separate columns

    ✭✭✭
    in Formulas and Functions

    Good Evening,

    I need help fixing the formula below, I need the checkbox to be empty if it is able to locate an entry when the month of January (in column named 'Please select the reporting month.') and the year 2025 (in column named 'Select the reporting year.') is selected from sheet named 'Section & Chapter Reporting Form', see image below.

    image.png

    The output will appear on a separate sheet named 'Missing Submissions Sheet (✔ means NO S...' and populate the column named 'Jan 2025 (INCOR…needs to pull 2025)'. A check box indicates a entry was NOT received and a EMPTY box indicates a entry WAS RECEIVED.

    image.png

    See the formula populated in column named 'Jan 2025 (INCOR…needs to pull 2025)' below.

    =IF(ISBLANK([Unique ID]@row), "", IF(COUNT(COLLECT({Section & Chapter Reporting Form Month}, {Section & Chapter Reporting Form Month}, "January", {Select the reporting year.}, "2025", {Section & Chapter Reporting Form Unique ID}, [Unique ID]@row)) > 0, 0, 1))

    Thanks for applying your brilliant minds to my problem,

    NCNW Inc.

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • Community Champion
      Answer ✓

      @NCNWIncData

      The #INVALID REF error means one of the cross sheet references is incorrect. Were you receiving an error with the formula you originally posted? I copied the references from there, so they should be the same. One or more of these either do not exist or are incorrect:

      {Section & Chapter Reporting Form Month}
      {Select the reporting year.}
      {Section & Chapter Reporting Form Unique ID}

      Site faviconFormula error messages | Smartsheet Learning Center

      · Share on FacebookShare on Twitter
    • ✭✭✭
      Answer ✓

      Good Evening Mr. Penticuff,

      First thank you for the feedback. It finally worked based on your initial suggestion about the reference sheet. For some reason when I changed the reference sheet it was not selecting the correct column, even after I did it so many times. I cleared my cache just in case and rewrote the formula, see below, and BAM it worked.

      =IF(ISBLANK([Unique ID#2]@row), "", IF(COUNTIFS({Section & Chapter Reporting Form Month}, "January", {Section & Chapter Reporting Form Range 1}, "2025", {Section & Chapter Reporting Form Unique ID}, [Unique ID#2]@row) > 0, 0, 1))

      Thank you very much,

      NCNWData Inc.

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion

      I think I understand what you are attempting to do. Let's try replacing the count/collection with a COUNTIFS, give this a try:

      =IF(ISBLANK([Unique ID]@row), "", IF(COUNTIFS({Section & Chapter Reporting Form Month}, "January", {Select the reporting year.}, "2025", {Section & Chapter Reporting Form Unique ID}, [Unique ID]@row) > 0, 0, 1))

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Good Evening Mr. Penticuff,

      Unfortunately, that did not work the prompt I received was #INVALID REF:

      image.png

      Thanks for attempting this,

      NCNW, Inc.

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      @NCNWIncData

      The #INVALID REF error means one of the cross sheet references is incorrect. Were you receiving an error with the formula you originally posted? I copied the references from there, so they should be the same. One or more of these either do not exist or are incorrect:

      {Section & Chapter Reporting Form Month}
      {Select the reporting year.}
      {Section & Chapter Reporting Form Unique ID}

      Site faviconFormula error messages | Smartsheet Learning Center

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Good Morning Mr. Penticuff,

      I have corrected the cross sheet reference, I noticed "{Select the reporting year.}" was the issue. I corrected this but now I am getting the #UNPARSEABLE Prompt.

      This is what I have currently:

      =IF(ISBLANK([Unique ID]@row), "", IF(COUNTIFS({Section & Chapter Reporting Form Month}, "January", {Section & Chapter Reporting Form Select the report}, "2025", {Section & Chapter Reporting Form Unique ID}, [Unique]@row) > 0, 0, 1))

      Do you think I need to add an AND statement between the January and 2025 conditions? I am going to try later today to see if it helps.

      Thanks for your thoughts on this matter,

      NCNW, Inc.

      · Share on FacebookShare on Twitter
    • Community Champion

      HI @NCNWIncData,

      I think the parenthesis after [Unique]@row might be the culprit. remove that and see if anything changes.

      Hope this helps,

      Dave

      · Share on FacebookShare on Twitter
    • Community Champion

      @NCNWIncData Your formula references [Unique ID]@row AND [Unique]@row. Is it possible this is a typo and the second one should be [Unique ID]@row also?

      · Share on FacebookShare on Twitter
    • ✭✭✭
      Answer ✓

      Good Evening Mr. Penticuff,

      First thank you for the feedback. It finally worked based on your initial suggestion about the reference sheet. For some reason when I changed the reference sheet it was not selecting the correct column, even after I did it so many times. I cleared my cache just in case and rewrote the formula, see below, and BAM it worked.

      =IF(ISBLANK([Unique ID#2]@row), "", IF(COUNTIFS({Section & Chapter Reporting Form Month}, "January", {Section & Chapter Reporting Form Range 1}, "2025", {Section & Chapter Reporting Form Unique ID}, [Unique ID#2]@row) > 0, 0, 1))

      Thank you very much,

      NCNWData Inc.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions