 # Nested array formula for a SUMPRODUCT operation

• 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
[TABLE="width: 500"]

[tr]

[td]

a1

[/td]

[td]

b1

[/td]

[/tr]

[tr]

[td]

a2

[/td]

[td]

b2

[/td]

[/tr]

[tr]

[td]

....

[/td]

[td]

....

[/td]

[/tr]

[tr]

[td]

....

[/td]

[td]

....

[/td]

[/tr]

[tr]

[td]

aN

[/td]

[td]

bN

[/td]

[/tr]

[/TABLE]

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 needs to be an array formula because the arrays in columns 1 and 2 are of different length every time. Imaging 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

• Re: Nested array formula for a SUMPRODUCT operation

I'm attaching an example.

## Files

• Re: Nested array formula for a SUMPRODUCT operation

Anyone can help on this one?

• Re: Nested array formula for a SUMPRODUCT operation

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

Where there is a will there are many ways. Finding one that works for you is the challenge!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!