INDEX/Collect function with two criteria returns #invalid value sometimes...

James Kosmas

Hi There,

I have a status update sheet that has many columns. The three relevant here are:

1) Date Column = {createdDate}

2) Checkbox = {PSR}

3) free text column = {Overall Commentary}

-----

In another sheet I am trying to collect the row with both the latest date and has the checkbox ticked.

Here is my formula:

=INDEX(COLLECT({Overall Commentary}, {createdDate}, MAX({createdDate}), {PSR}, 1), 1)

----

It seems to only work if one of the checkbox rows IS the max date. If there is a higher date that does not have a checkbox ticked it returns the error. I have double checked all my references and have tested it with dummy data and with one criteria at a time and always get the same results, which is it will work if one of the rows with a ticked checkbox is also the highest date. I have also reversed the formula to: =INDEX(COLLECT({Overall Commentary}, {PSR}, 1, {createdDate}, MAX({createdDate})), 1) and get the same results. Any help would be greatly appreciated.

----

Edit: I need to filter rows to only those that have the checkbox column ticked, then select MAX date from this filtered list. Seems like only the max date is used as the "filter" and just throws error if checkbox is not ticked, I need it to be the other way around.

Thanks!

Find more posts tagged with

Sheets

Formulas

Accepted answers

Kelly Moore

Hey James

Sorry about that. Try this

=IF(AND(PSR@row=1,Created@row = MAX(COLLECT(Created:Created, PSR:PSR, 1))), 1)

Kelly

All comments

Kelly Moore

Hey @James Kosmas

I understand the formula throws an error when you don't have data that matches your criteria. As your formula is written, you will only have results, as you have experienced, when the max date is within your checked rows.

A helper column (a checkbox will work) in your source sheet will help you accomplish your task. It is checked when the Created@row is the max date where the row has has PSR checked. Be sure to change the column names in the formula to reflect your actual column names.

[Max Date]

=IF(Created@row = MAX(COLLECT(Created:Created, [your source sheet PSR column]:[your source sheet PSR column], 1)), 1)

Then your desired formula in your status update sheet becomes

=INDEX(COLLECT({Overall Commentary}, {Source Sheet Max Date}, 1, {PSR}, 1), 1)

You will need to manually insert the new {Max Date} cross-sheet reference into your sheet.

Does this work for you?

Kelly

James Kosmas

Hi @Kelly Moore

Thanks so much for your response.

It almost works...

=IF(Created@row = MAX(COLLECT(Created:Created, [your source sheet PSR column]:[your source sheet PSR column], 1)), 1)

this will find the row with the highest date of all the ticked rows (even if there is a higher date that is not ticked). This is fantastic, but when it does this the helper row also selects any rows that have that same date (even if they are not ticked).

Screenshot attached for reference.

Thanks again!

Screen Shot 2022-03-29 at 9.07.03 pm.png

Kelly Moore

Hey James

Sorry about that. Try this

=IF(AND(PSR@row=1,Created@row = MAX(COLLECT(Created:Created, PSR:PSR, 1))), 1)

Kelly

James Kosmas

Thanks @Kelly Moore that worked!!

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