Defining an Array Within a Formula

[Nick6425]

Greetings all,

I'm looking to multiply the values returned by these 5 rounded sumproducts by the array created by "Volume!Y$24:Y$28/Volume!Y$63", but I don't know how to get the formula to recognize those 5 returned values as an array. Thank you for our help in advance!

=IFERROR(SUMPRODUCT(ROUNDDOWN(SUMPRODUCT((HPU!$B$6:$B$58=$B6)*(HPU!$D$5:$J$5=YEAR(Y$3))*HPU!$D$6:$J$58),1),ROUNDDOWN(SUMPRODUCT((HPU!$B$6:$B$58=$B6)*(HPU!$M$5:$S$5=YEAR(Y$3))*HPU!$M$6:$S$58),1),ROUNDDOWN(SUMPRODUCT((HPU!$B$6:$B$58=$B6)*(HPU!$V$5:$AB$5=YEAR(Y$3))*HPU!$V$6:$AB$58),1),ROUNDDOWN(SUMPRODUCT((HPU!$B$6:$B$58=$B6)*(HPU!$AE$5:$AK$5=YEAR(Y$3))*HPU!$AE$6:$AK$58),1),ROUNDDOWN(SUMPRODUCT((HPU!$B$6:$B$58=$B6)*(HPU!$AN$5:$AT$5=YEAR(Y$3))*HPU!$AN$6:$AT$58),1),Volume!Y$24:Y$28/Volume!Y$63),0)*Volume!Y$64

 

[Marcelo Branco]

To create a vertical array with 5 single results you can try something like

CHOOSE({1;2;3;4;5},result1,result2,result3,result4,result5)

M.

 

[Nick6425]

Thank you, Marcelo. This worked!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.