IF statement inside a COLLECT

SJ Sellers

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.

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

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. 👍️