IF & ISBLANK with two Criteria in separate columns

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.

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.

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.

Find more posts tagged with

Accepted answers

Carson Penticuff

@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}

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc-invalid-ref

NCNWIncData

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.

All comments

Carson Penticuff

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))

NCNWIncData

Good Evening Mr. Penticuff,

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

Thanks for attempting this,

NCNW, Inc.

Carson Penticuff

@NCNWIncData

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

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc-invalid-ref

NCNWIncData

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.

DKazatsky2

HI @NCNWIncData,

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

Hope this helps,

Dave

Carson Penticuff

@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?

NCNWIncData

Good Evening Mr. Penticuff,

Thank you very much,

NCNWData Inc.

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, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.