IF statement inside a COLLECT

Why do all of these formulas work:

SUM(COLLECT({Value}, {Location}, Location@row))

SUM(COLLECT({Value}, {Location}, <>""))

SUM(COLLECT({Value}, {Location}, IF(index@row=1, "Boston", Location@row)))

But this does not:

SUM(COLLECT({Value}, {Location}, IF(index@row=1, <>"", Location@row)))

I'm trying to understand this behavior - I have a hairy problem to solve which would be solved more easily with the bolded approach.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try an @cell reference.

=SUM(COLLECT({Value}, {Location}, IF(index@row=1, @cell <>"", Location@row)))

SJ Sellers

😍

That worked. Thank you so much @Paul Newcome .

I'm kicking myself for not thinking of it myself 🧐

All comments

Paul Newcome

Are you getting an error?

SJ Sellers

Hi @Paul Newcome

Thanks for checking in.

Yes, I am getting an error. See the screenshot below:

I would expect 900 in the cell where the error is. I just don't understand why using an IF statement is perfectly acceptable (as shown in Formula 3) , both of the condition statements work separately (Formulas 1 & 2), but IF won't accept this particular condition. I have also tried using Location@row as the true condition, and <>"" as the false condition - no joy.

Any ideas? Or alternatives?

I understand I could use IF(index@row = 1, Formula2, Formula1)..... but the above is just a simple example for troubleshooting. In my "real" sheet, this less ideal approach results in a formula over 550 characters long, and as you can imagine, is very hard to troubleshoot or change when business assumptions change.

Paul Newcome

Try an @cell reference.

=SUM(COLLECT({Value}, {Location}, IF(index@row=1, @cell <>"", Location@row)))

SJ Sellers

😍

That worked. Thank you so much @Paul Newcome .

I'm kicking myself for not thinking of it myself 🧐

Paul Newcome

Happy to help. 👍️

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.