Is it possible a nested array formula within another array formula?

I have two columns of data with N data each, being N variable. Be data in column 1: {a1, a2, ....., aN} and in column 2: {b1, b2, .... bN}. Each figure is in one cell
a1 b1
a2 b2
.... ....
.... ....
aN bN

I need an array formula to be able to perform the following calculation:
a1*(1+b1)*(1+b2)*...*(1+bN)+a2*(1+b2)*(1+b3)*...*(1+bN)+.....+aN*(1+bN)

It looks like I need a nested array formula because the arrays in columns 1 and 2 are of variable length (N can vary). For example,iImaging data in columns A and B from rows 1 to 10 (N=10), the formula I tried was:
{=SUMPRODUCT(A1:A10,PRODUCT(1+B1:B10))}
but it didn't work. Excel just multiply every data in column A times (1+b1)*(1+b2)*....*(1+bN)=PRODUCT(1+B1:B10). However I'm looking for a SUMPRODUCT function where in each new factor the multiplication is adding a new element of the form (1+bi) -see formula above-.
I would appreciate your support.

See example attached. I need just one formula. I know it is possible with a helper column or with an UDF, but I'm looking for a formula.
Thanks

Crossposted. Please post all links to other forums where you posted this question.

When I try to post the link the system does not allow me to have any links in the message. How should I do it?

Hi NBVC, how can I post the links? When I copy and paste the link and press Post Reply the system does not allow to copy internet links.

Zapillo said:

how can I post the links?

Click to expand...

try as text (www. domain. com/page.html)

excelforum.com
I was trying to get new hints to this unsolved question. Nobody there gave an answer and it's been a frustration so far with this problem

Zapillo said:

excelforum.com

Click to expand...

You did not well understood me. Link be targeted to a page where you are asked the same question.
So complete address URL.

Like:
excelforum.com/ excel-general/1156630-table-formatting.html

excelforum.com/excel-formulas-and-functions/1156444-is-it-possible-a-nested-array-within-an-array-formula.html

suma and product variable value

Zapillo said:

For example,iImaging data in columns A and B from rows 1 to 10 (N=10)

Click to expand...

If I understood you well
example, try in H1 cell

=A1*(1+B1)*(1+B2)*(1+B3)*(1+B4)*(1+B5)*(1+B6)*(1+B7)*(1+B8)*(1+B9)*(1+B10)+A2*(1+B2)*(1+B3)*(1+B4)*(1+B5)*(1+B6)*(1+B7)*(1+B8)*(1+B9)*(1+B10)+A3*(1+B3)*(1+B4)*(1+B5)*(1+B6)*(1+B7)*(1+B8)*(1+B9)*(1+B10)+A4*(1+B4)*(1+B5)*(1+B6)*(1+B7)*(1+B8)*(1+B9)*(1+B10)+A5*(1+B5)*(1+B6)*(1+B7)*(1+B8)*(1+B9)*(1+B10)+A6*(1+B6)*(1+B7)*(1+B8)*(1+B9)*(1+B10)+A7*(1+B7)*(1+B8)*(1+B9)*(1+B10)+A8*(1+B8)*(1+B9)*(1+B10)+A9*(1+B9)*(1+B10)+A10*(1+B10)

or
=A1*PRODUCT(1+B1:$B10)+A2*PRODUCT(1+B2:$B10)+A3*PRODUCT(1+B3:$B10)+A4*PRODUCT(1+B4:$B10)+A5*PRODUCT(1+B5:$B10)+A6*PRODUCT(1+B6:$B10)+A7*PRODUCT(1+B7:$B10)+A8*PRODUCT(1+B8:$B10)+A9*PRODUCT(1+B9:$B10)+A10*PRODUCT(1+B10:$B10)

or
=A1*PRODUCT(1+B1:$B$10)+A2*PRODUCT(1+B2:$B$10)+A3*PRODUCT(1+B3:$B$10)+A4*PRODUCT(1+B4:$B$10)+A5*PRODUCT(1+B5:$B$10)+A6*PRODUCT(1+B6:$B$10)+A7*PRODUCT(1+B7:$B$10)+A8*PRODUCT(1+B8:$B$10)+A9*PRODUCT(1+B9:$B$10)+A10*PRODUCT(1+B10:$B$10)

Click to expand...

navic said:

If I understood you well
example, try in H1 cell

Click to expand...

Hi Navic,

Thanks for trying but that's not the point. I know how to build up those formulas. The key point is to have an array formula so that when the ranges in columns A and B the formula will provide the result without having to modify the components of the formula itself. In fact, I need this formula in one column so that in every cell it will get different ranges from columns A and B and will provide the result.

I tried the array formula: {=SUMPRODUCT(A1:A3,PRODUCT(1+B1:B3))} assuming that for every entry in the A-range the formula would calculate the expected result, however it didn't work because for every Ai then we would need to have like another array formula embedded in the first array formula.