multiplying a set of numbers by an array and summing

xtapalat

New Member
Joined
Aug 8, 2010
Messages
15
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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?

Disable AdBlock

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
Back
Top