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

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

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,449
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:

xtapalat

New Member
Joined
Aug 8, 2010
Messages
15
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
Joined
Nov 7, 2006
Messages
8,424
Office Version
  1. 2019

ADVERTISEMENT

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?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,449

ADVERTISEMENT

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
Joined
Aug 8, 2010
Messages
15
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
Joined
Sep 2, 2009
Messages
16,449
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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?

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
Top