# multiplying a set of numbers by an array and summing

#### xtapalat

##### New Member
I have a problem where a set number of new customers are coming online in each of the next five years (populated in cells A1:A5 as follows): 5, 3, 2, 4, 3. I am predicting that over five years the sales from a new customer will grow as follows (represented in cells B1:B5): \$100, \$125, \$175, \$225, \$275, with \$100 representing the sales to a customer in his first year with our company and \$275 representing the sales in his 5<SUP>th</SUP> year.
The question I have is how to create a formula that sums my total expected sales over each of the next five years. I can do it through simple brute force, but is there an elegant way? I can see the spreadsheet growing down the road and I think if I'm just manually multiplying all the combinations I will regret it later.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### AlphaFrog

##### MrExcel MVP
Try using the SUMPRODUCT function

=SUMPRODUCT(A1:A5, B1:B5)

This will give you the total for (A1*B1)+(A2*B2)+(A3*B3)+(A4*B4)+(A5*B5)
Is that what you want?

Last edited:

#### mikerickson

##### MrExcel MVP
Does MMULT(A1:A5,TRANSPOSE(B1:B5)) return the value you seek.

#### xtapalat

##### New Member
Thanks to both of you, but those don't quite do what I want. Let me be more explicit. Here is what I am trying to do:
Y1: 5*100 (i.e. A1*A2)
Y2: 5*125 + 3*100 (i.e. A1*B2 + B1*A2)
Y3: 5*175 + 3*125 + 2*100 (i.e. A1*C2 +B1*B2 +C1*A2)
etc. through Year 5.

Is there an elegant solution to that?

#### Special-K99

##### Well-known Member

In your initial description you say "cells A1:A5 as follows): 5, 3, 2, 4, 3", ie the table goes down rows, but in your calculation examples

A1=5
B1=3
C1=2

from

Y1: 5*100 (i.e. A1*A2)
Y2: 5*125 + 3*100 (i.e. A1*B2 + B1*A2)
Y3: 5*175 + 3*125 + 2*100 (i.e. A1*C2 +B1*B2 +C1*A2)
etc. through Year 5

(Note colours)

ie the table is going across columns

Can you confirm which is correct the original description or the examples?

#### xtapalat

##### New Member
Sorry for the inconsistency. Just follow the examples.

#### AlphaFrog

##### MrExcel MVP

Is this it?
Excel Workbook
ABC
15\$100\$11,075
23\$125*
32\$175*
44\$225*
53\$275*
Sheet

Or Is this what you wanted?
Excel Workbook
ABC
15\$100\$4,500
23\$125\$2,400
32\$175\$1,350
44\$225\$2,000
53\$275\$825
6\$11,075
Sheet

Last edited:

#### xtapalat

##### New Member
The structure in your second table is what I'm trying to do, but the results don't match what I get when I do it manually. The results for your column C that I'm trying to get (starting with C1) are :500, 925, 1450, 2300, 3200.

500 is calculated as A1*B1 (5 new customers is all you have in Year 1, they bring in the first year revenue of \$100/customer).
925 is calcuated as A1*B2 + A2*B1 (in year 2, your 5 customers from year 1 are now second year customers so bring in revenue of \$125/customer or \$625. You also have 3 new customers bringing in the first year rate of \$100/customer bringing the total up to \$925).

I appreciate you (or anyone) taking a look at this.

#### AlphaFrog

##### MrExcel MVP
Pehaps you could just list the long brute force formulas using my 2nd table as a reference. Please list each formula you would use in column C.

Replies
2
Views
695
Replies
3
Views
1K
Replies
8
Views
177
Replies
3
Views
606
Replies
3
Views
333

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,425
Messages
5,831,559
Members
430,075
Latest member
Tuturino

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back