[mso] Re: Excel question.
- From: "David Smart" <smartware@xxxxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Wed, 22 Nov 2006 22:37:51 +1100
Your attachment didn't make it, so ...
By "running total", I assume you want a totals column, with a figure on each
row, where that figure is the sum of all the sales by that person down to
that point.
For instance
3310 100 100
3310 127 227
3303 86 86
3310 875 1102
3310 887 1989
3303 352 438
3303 34 472
3310 762 2751
3310 634 3385
3303 234 706
3303 412 1118
Where the first column (A2:A12) has your person number. The next column
(C2:C12) has the individual sales amounts. The last column (E2:E12) is the
intermingled running totals for the two people.
This can be done with an anchored SumIF. For instance, E10 is
=SUMIF(A$2:A10,A10,C$2:C10)
The trick is having the anchor on the top (Row 2) items, but not on the
bottom of the range (Row 10 in this case). You can copy/paste this formula
all down the E column to give you an interspersed running total column as
tall as you want.
Note that it is not limited to just two people. It can easily handle more.
For instance, just changing a couple of Column A numbers:
3310 100 100
3310 127 227
3303 86 86
3300 875 875
3310 887 1114
3303 352 438
3303 34 472
3310 762 1876
3300 634 1509
3303 234 706
3303 412 1118
gives you the extra person: 3300.
Regards, Dave S
----- Original Message -----
From: "Dallas Stearns" <dallas_1973@xxxxxxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Sent: Wednesday, November 22, 2006 9:33 AM
Subject: [mso] Re: Excel question.
>
> I will attach one of the reports. The A/M is short for account manager-
> there are two that I deal with- one with the number 3310, and the other
> 3303. The column next to that number is the OTB (short for ONE TIME BUY-
> which is a term when a client comes in and purchases only one item that
> may
> have been left off the inital order) The A/M still get's credit for it-
> but
> my company doesn't track it- I have to. So i have 2 A/ms and for each OTB
> i
> have to keep a running total. In the spreadsheet i sent- i have been
> MANUALLY entering these values. I know that there has to be a way I can
> makeit do it for me. That's why i asked the group. I color coded them to
> show you what I mean. I have to arrange this by date- otherwise it would
> be
> easy to do what I want if I could arrange by account manager.
>
> Thank you soo much. ALSO- where can I find your book on excel?
>
> Dallas
> ----------------------------------------------------------------------------
> From: "Linda F. Johnson" <linda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
> Reply-To: mso@xxxxxxxxxxxxx
> To: <mso@xxxxxxxxxxxxx>
> Subject: [mso] Re: Excel question.
> Date: Tue, 21 Nov 2006 17:10:44 -0500
>>I'm assuming their number appears on each row that shows their sales? And,
>>that the number is always in the same column?
>>
>>You can sum all the sales that refer to each salesman's number, either by
>>creating a table and using the DSUM function, but it might be easier to
> just
>>do it as an array formula.
>>
>>Read this article I wrote on array formulas and see if this gives you what
>>you need:
>>http://personal-computer-tutor.com/arrayformulas.htm
>>
>>You need to see the part which shows you how to sum something in Column A,
>>based on what is in Column B
>>
>>
>>Linda F. Johnson
>>Linda's Computer Stop
>>Author, MOS: Excel 2003 Study Guide, published by John Wiley and Sons
>>http://personal-computer-tutor.com
>>
>>-----Original Message-----
>>From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
>>Of Dallas Stearns
>>Sent: Tuesday, November 21, 2006 3:50 PM
>>To: mso@xxxxxxxxxxxxx
>>Subject: [mso] Excel question.
>>
>>
>>ok, I'm having an issue. I have tried everything I can think of- hopefully
>>someone from this news group can help. I have a pretty simple excel
>>spreadsheet. It's a sales tracking form. I have 2 sales reps that are
>>assigned a number to identify them (one is 3310, and the other is 3303)
>>
>>I track all the sales they make. AND in the LAST column there is a
>>"running
>>total" from each rep. I have tried an "If" formula- and I can't make it
>>work. Is there something someone might be able to suggest. I don't want
>>thetotal to be the TOTAL sales- but rather a running total from each rep.
>>
>>
>>
>>thanks! Dallas
>>
>>
>>*************************************************************
>>You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>>
>>To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>>
>>To Unsubscribe from this group, visit the group's homepage and use the
> dropdown menu at the top. This will allow you to unsubscribe your email
> address or change your email settings to digest or vacation (no mail).
>>//www.freelists.org/webpage/mso
>>
>>If you are using Outlook and you see a lot of unnecessary code in your
> emailmessages, read these instructions that explain why and how to fix it:
>>http://personal-computer-tutor.com/abc3/v28/greg28.htm
>>*************************************************************
>
>
> ----------------------------------------------------------------------------
> Talk now to your Hotmail contacts with Windows Live Messenger.[1]
>
> --- Links ---
> 1 http://g.msn.com/8HMAENUS/2749??PS=47575
> *************************************************************
> You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, visit the group's homepage and use the
> dropdown menu at the top. This will allow you to unsubscribe your email
> address or change your email settings to digest or vacation (no mail).
> //www.freelists.org/webpage/mso
>
> If you are using Outlook and you see a lot of unnecessary code in your
> email messages, read these instructions that explain why and how to fix
> it:
> http://personal-computer-tutor.com/abc3/v28/greg28.htm
> *************************************************************
>
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
