I am trying to duplicat a paper form used in my work. It involves tracking
sales, adding them and calculating commissions. Let me try to put this
briefly. A customer may purchase ads in multiple books. My problem is
figuring out how to make the spreadsheet 1) determine that there is multiple
book sales 2) calculate all of them into one total field 3) how to place them
on the sheet to work 4) and how to lay it out to make it all work if there is
not multiple sales. Make sense? Later when printing the "report" actual for
to turn in, I will need to not show some of the "extra fields that make it
work, but I think I can do that. For now I need to make it work. Remember, I
am new to Excel and a only a bit familiar with terminology.

I would need some examples to make sense of this.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"sparhawr45" wrote in message
...
I am trying to duplicat a paper form used in my work. It involves tracking
sales, adding them and calculating commissions. Let me try to put this
briefly. A customer may purchase ads in multiple books. My problem is
figuring out how to make the spreadsheet 1) determine that there is
multiple
book sales 2) calculate all of them into one total field 3) how to place
them
on the sheet to work 4) and how to lay it out to make it all work if there
is
not multiple sales. Make sense? Later when printing the "report" actual
for
to turn in, I will need to not show some of the "extra fields that make it
work, but I think I can do that. For now I need to make it work. Remember,
I
am new to Excel and a only a bit familiar with terminology.

Ok let me try and keep it fairly simple.
Among other headings the primary was I am working with a Name, Phone,
Book Code (designates which book(s) they bought),Exist rev. (last years money
spent), Increase, New,And Total Sale. An couple examples of possible sales
a Cust #1 had $400 in existing from last year in 1 book. This year
increases the size so the overall sale increase by $200. That one is easy to
make work. I enter the figures in the appropriate headings and make the sheet
total them, carry the total over to calculate a commission. However, Cust. #2
Has $400 existing in Book 1, $300 in Book 2, and $200 in Book 3. This year
expands the size of the ads so for examples sake increases all 3 books by
$100. And places the same expanded ad in a new book ( Book #4) for a NEW sale
over last year of $400.
So here is how it would look on the form:
Name Phone Book C. Exist Inc. New Total
#1 YYYY #1 400 200 600
#2 XXXX #1 400 100 1600
" " #2 300 100
" " #3 200 100
" " #4 400
Bernard if you can see my dilema, Cust # 1 is easy because its all on one
line and flows over easy. #2 however has 4 lines of data entry but I must get
them all into one total on the customers 1st entry line to perform other
calculation on them. Those ohter calculation are no problem. I need to know
( if possible ) how to thell the sheet to perform the calculations of both
types of customers # 1 & #2. I don't know how to tell it to recognize the
difference betweeen the two and when it does perform the calculations to get
a total sale. Makes Sense??? I have messed with "IF" & SUMIF and to no avail.
HELP!! if you can.

"Bernard Liengme" wrote:

I would need some examples to make sense of this.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"sparhawr45" wrote in message
...
I am trying to duplicat a paper form used in my work. It involves tracking
sales, adding them and calculating commissions. Let me try to put this
briefly. A customer may purchase ads in multiple books. My problem is
figuring out how to make the spreadsheet 1) determine that there is
multiple
book sales 2) calculate all of them into one total field 3) how to place
them
on the sheet to work 4) and how to lay it out to make it all work if there
is
not multiple sales. Make sense? Later when printing the "report" actual
for
to turn in, I will need to not show some of the "extra fields that make it
work, but I think I can do that. For now I need to make it work. Remember,
I
am new to Excel and a only a bit familiar with terminology.

Bernard,
Additional thought. I also need to pull totals for the headings NEW,
INCREASE, and EXISTING for each book if possible. That makes it more complex
I suppose. Thanks again for your interest in helping.

"Bernard Liengme" wrote:

I would need some examples to make sense of this.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"sparhawr45" wrote in message
...
I am trying to duplicat a paper form used in my work. It involves tracking
sales, adding them and calculating commissions. Let me try to put this
briefly. A customer may purchase ads in multiple books. My problem is
figuring out how to make the spreadsheet 1) determine that there is
multiple
book sales 2) calculate all of them into one total field 3) how to place
them
on the sheet to work 4) and how to lay it out to make it all work if there
is
not multiple sales. Make sense? Later when printing the "report" actual
for
to turn in, I will need to not show some of the "extra fields that make it
work, but I think I can do that. For now I need to make it work. Remember,
I
am new to Excel and a only a bit familiar with terminology.

Actually, your desire to see the totals for existing, increase and new almost
make it easier, at least they make the formulas more readable.

Assuming data starts on row 2, and columns are laid out like this:
A B C D E F
name Tel# BookC exist Increase New

then we'll use G, H, I and J like this:
G H I J
Total Exist Total Increase Total New Grand Total

In G2 put this formula:
=IF(COUNTIF(A$1:A1,A2)=0,SUMIF(A2:A$65536,A2,D2:D$ 65536),"")
In H2 put this one:
=IF(COUNTIF(A$1:A1,A2)=0,SUMIF(A2:A$65536,A2,E2:E$ 65536),"")
in I2 put this one:
=IF(COUNTIF(A$1:A1,A2)=0,SUMIF(A2:A$65536,A2,F2:F$ 65536),"")
finally, in J2, enter this formula:
=IF(COUNTIF(A$1:A1,A2)=0,SUM(G2:I2),"")

Now you can just fill them down the sheet as far as you need to go and the
totals for each Name in column A will appear in the row where that name
appears for the first time in that column.

Here's how it will look with some data tossed in:
A B C D E F G H I J
1 column titles are in row one.............
2 R - 1 400 200 0 400 200 0 600
3 B - 1 400 100 0 900 300 400 1600
4 B - 2 300 100 0
5 B - 3 200 100 0
6 B - 4 400
7 C - 1 200 0 0 200 200
8 D - 1 100 40 100 40 0 140
9 E - 4 0 0 600 0 0 600 600
10

If this is not what you needed, let me know. I've uploaded a workbook with
this all in it at: http://www.jlathamsite.com/uploads/forSparhawr45.xls that
you can download and examine and see it in action. Has my contact info in it
also.


"sparhawr45" wrote:

I am trying to duplicat a paper form used in my work. It involves tracking
sales, adding them and calculating commissions. Let me try to put this
briefly. A customer may purchase ads in multiple books. My problem is
figuring out how to make the spreadsheet 1) determine that there is multiple
book sales 2) calculate all of them into one total field 3) how to place them
on the sheet to work 4) and how to lay it out to make it all work if there is
not multiple sales. Make sense? Later when printing the "report" actual for
to turn in, I will need to not show some of the "extra fields that make it
work, but I think I can do that. For now I need to make it work. Remember, I
am new to Excel and a only a bit familiar with terminology.