# Creating a running list of sales numbers

#### dlem7

##### New Member
Hi folks,

I'm currently working on an excel sheet where I am attempting to summarize sales numbers.

In this scenario I have what I call "Account Behavior". Basically when an account is registered, I expect some sort of shopping behavior at a store. In month 1, they will spend \$100. In month 2, they will spend \$50. In month 3 they will spend \$30 (so on and so forth).

Next I have another field I call "New account growth". This is a field that says our store expects 10 new accounts in month 1, 12 in month 2, 15 in month 3, etc.

What I need help with is summarizing this data. I want to create a third field called "Total Sales by month". In the example outlined above, my first month would simply be my first ten accounts making a purchase of \$100 each (10*100=\$1000). The second month would then have 10 accounts making a purchase of \$50, and 12 new accounts making a purchase \$100. My formula would then be 10*50+12*100 = \$1700.

My biggest issue is that I'm working with 10 years of projections split out by month so writing out the formulas is extremely tedious. I came here hoping to find a faster way/method for summarizing this data.

Below I listed a short example. I hope the formatting comes through
 Month on Book 1 2 3 4 5 6 New Account Sales \$100 \$50 \$20 \$18 \$16 \$14 Calendar Month Jan Feb Mar Apr May Jun Account Openings 10 12 5 8 11 7 Total Sales 10*\$100 10*\$50+12*\$100 10*\$20+12*\$50+5*\$100 ? ? ?

<tbody>
</tbody>

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
It looks like you can write your equation with relative references to the cells above. Then copy this across the whole row.

Click in the first Total Sales Cell and type =
Then click the account openings cell above it
Then *
Then click the cell for New Account Sales

You should end up with something like this:
Code:
``=B4*B2``

In column 2 make a similar equation that also adds the total before it.

This should look like:
Code:
``=(C4*C2)+B5``

Now you can repeat the equation in the second cell across the whole row.
With your second cell selected: grab the little box in the lower right hand corner of the cell and drag it to the right. This will make relative references in all the cells you drag it to.

Magic.

Let me know if that is what you were looking for.

It looks like you can write your equation with relative references to the cells above. Then copy this across the whole row.

Click in the first Total Sales Cell and type =
Then click the account openings cell above it
Then *
Then click the cell for New Account Sales

You should end up with something like this:
Code:
``=B4*B2``

In column 2 make a similar equation that also adds the total before it.

This should look like:
Code:
``=(C4*C2)+B5``

Now you can repeat the equation in the second cell across the whole row.
With your second cell selected: grab the little box in the lower right hand corner of the cell and drag it to the right. This will make relative references in all the cells you drag it to.

Magic.

Let me know if that is what you were looking for.

Unfortunately this doesn't get to what I need but thank you for taking the time to reply.

In the example you listed above the 12 new accounts in month 2 are making a purchase of \$50 and the first 10 accounts arent making a purchase of \$100 again.

My excel formula for the total sales in month 1 looks like "B2*B4". Month 2 should look something like "C2*B4+B2*C4". Month 3 would look like "D2*B4+C2*C4+B2*D4"

Replies
2
Views
351
Replies
20
Views
651
Replies
0
Views
211
Replies
11
Views
486
Replies
1
Views
207

1,196,055
Messages
6,013,129
Members
441,750
Latest member

### 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.

### Which adblocker are you using?

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

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