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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

AlphaFrog

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

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,271

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,271
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,773
Messages
5,513,349
Members
408,948
Latest member
robertjohn18

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top