SUMPRODUCT for more than 2 lists/arrays?

[Zsolt_Bako]

Hi, is it possible to use the SUMPRODUCT() function on more than two lists or arrays?

In sheets/excel SUMPRODUCT() works for 30 arrays but here in coda it’s limited to 2. Do you have any idea how could I achieve the results in coda? Is there a workaround?

In my use case I have a table with 3 columns and in each row I have equally sized list of numbers in each cell (see below). The last column would be the SUMPRODUCT() of the 3 lists in each row. In this example below I used SUMPRODUCT() for the first two columns (since the 3rd one’s value is 1 everywhere) but I’d like to include a third column as well:

Thanks a lot!

[Dalmo_Mendonca]

Try this:

Sequence(1,List1.Count()).FormulaMap(Product(List1.Nth(CurrentValue),List2.Nth(CurrentValue),List3.Nth(CurrentValue))).Sum()

Turns out by combining Sequence and FormulaMap you have a classic for loop!

[Zsolt_Bako]

Thank you very much!

Unfortunately I didn’t get a notification when you replied so meanwhile I came up with the exact same solution but your answer could have save me a lot of time and frustration

I’m also posting here your Tips&Hacks post about the general idea behind the solution:

Simulate a For Loop (Reference position inside FormulaMap)

So I was struggling with a problem where I needed to evaluate multiple lists simultaneously. A classic use case for a loop. Coda doesn’t have built-in formulas for classic loops such as for or while. The closest thing being FormulaMap. It takes a list as input and runs every item through an expression. Inside the expression, you use CurrentValue to reference the current item being evaluated. As a result, CurrentValue can be of any type (row, date, etc). But there’s no obvious way to get the ke…

It’s a really smart hack that can come handy in a lot of different situations.
Thanks again!