How to mix dynamic and constant array in Excel

Question

How can I mix a constant array of values to a dynamic array in Excel, I am looking through a non-VBA method to do so. Let me explain the scenario-

Say, I have a column of 7 values. I can generate an array of same number of values if I want to test a condition for each of these. Now I want to multiply (sumproduct) the resulting array with another column having actually n+1 values (8 in this case) and I know that last value to be added to this dynamic array is constant say, FALSE (0).

In the above example I actually want to add one constant 0/False at the end of dynamic array ($A1:$A7<5), without adding a dummy/hidden row/cell/column? Can I do it?

I have tried these variations but none of these work

=SUMPRODUCT({($A$1:$A$7<5);0}*(E1:E8))

=SUMPRODUCT({($A$1:$A$7<5),0}*(E1:E8))

{=SUMPRODUCT({($A$1:$A$7<5);0}*(E1:E8))} # array formula

question from:https://stackoverflow.com/questions/65860974/how-to-mix-dynamic-and-constant-array-in-excel

Answer 1

I don't know a way of actually adding extra elements to an existing array in Excel (as you can in Google Sheets), but you can work round it by testing whether the current row is less than or equal to the number of rows in column A, and taking a default value if not.

In Microsoft 365:

=SUM(IF(ROW(E1:E8)<=ROWS(A1:A7),A1:A7<5)*E1:E8)

In Excel 2019, the same formula but it has to be array-entered using CtrlShiftEnter

The false default value in the above formula comes from the fact that there is only one argument in the If statement, so if the number of rows is greater than 7, it gives False by default. You could change this, e.g. to 1, by putting in the second argument e.g.

=SUM(IF(ROW(E1:E8)<=ROWS(A1:A7),A1:A7<5,1)*E1:E8)

A more dynamic version would be

=SUM(IF(ROW(E1:INDEX(E:E,COUNT(E:E)))<=ROWS(A1:INDEX(A:A,COUNT(A:A))),A1:INDEX(A:A,COUNT(A:A))<5)*E1:INDEX(E:E,COUNT(E:E)))

which again has to be array-entered if you aren't using Microsoft 365.

In Microsoft 365, you could shorten this considerably using Let():

=LET(A,A1:INDEX(A:A,COUNT(A:A)),E,E1:INDEX(E:E,COUNT(E:E)),SUM(IF(ROW(E)<=ROWS(A),A<5)*E))