Google-sheets – SUMPRODUCT inside ARRAYFORMULA in Google Sheets

google sheets

I have a SUMPRODUCT equation that works fine on one row:

=SUMPRODUCT(O4:O10-O3:O9,E2-P4:P10,N(E2>P4:P10))

However, instead of copying it for each E row I want to use ARRAYFORMULA to automatically generate the values using E2:E.

When I try the following:

=ARRAYFORMULA(SUMPRODUCT(O4:O10-O3:O9,E2:E-P4:P10,N(E2:E>P4:P10)))

I get the error Array arguments to MINUS are of different size.

I know there is another question asking something similar, but there is no explanation of the formula in the answer so I cannot apply it to my situation easily.

Best Answer

The error is caused by this part

E2:E-P4:P10

The end part in E2:E is an open reference, meaning that it will be take the last row of the sheet.

One possible solution could be to change E2:E by E2:E8 or something similar in order to make this part of the formula have the same number of rows as P4:P10

Related Solutions

Google-sheets – Using SUMPRODUCT with ARRAYFORMULA

An array solution could be:

=ArrayFormula(IF(LEN(A:A),COUNTIF(Sheet1!A:A&CHAR(9)&Sheet1!B:B,A:A&CHAR(9)&B:B),))

although it might be better to generate the unique counts in the QUERY itself:

=QUERY(Sheet1!A1:C10,"select A, B, count(C) where A != '' group by A, B order by A asc label count(C) ''",0)

This relies on there being a column C present in Sheet1 (it doesn't matter what is actually in column C). However, if wasn't viable having a column C, then it could be worked around.

Google-sheets – How to calculate with cells that contain two values separated by linebreak

You need to split the content of cells, then do the math (using arrayformula) and finally join again:

=join(char(10), arrayformula(24*(split(D26, char(10))-split(C26, char(10)))))

Here char(10) is the "new line" control character.

The above will preserve the format of the original cells (e.g., time will remain time). Note that the final result (after join) is just a text string, which cell formatting will not change. If you need the result to be formatted as a number, do it explicitly with value:

=join(char(10), arrayformula(24*value(split(D26, char(10))-split(C26, char(10)))))

Remark. Putting two values in one cell is generally a bad idea if you plan to continue calculating with them. It's mostly a thing we do with text or with final output.

Best Answer

Related Solutions

Google-sheets – Using SUMPRODUCT with ARRAYFORMULA

Google-sheets – How to calculate with cells that contain two values separated by linebreak

Related Topic