Formula for totaling column

[Guest]

Have 13 column by 10,000 row spred sheet. column A represents a salesmans
number while column B thru M represent different products that the sales
person can sell and the quantity that's sold.
Sp Prod Prod Prod etc
A B C D etc.

206 5 8 2
206 1 3 5
109 2 2 1
206 3 1 8
303 1 2 1
303 2 5 3
using a formula courtesy of Peo Sjoblom =SUMPRODUCT(--(LEFT(A2:A10000)="2"))
I can total the number of sales of a salesperson but also need to total
their individual product sales. Example- salesperson 206 had 3 sales with
product totaling B 9, Product C 12, Product D 15, etc. My summary sheet has
a cell for each salesperson's product total.
Can it be done?
Thank You in Advance.
dbglass

 

[Bob Phillips]

Definitely a job for a pivot table.



See http://www.contextures.com/xlPivot01.html
and http://www.peltierteh.com/Excel/Pivots/pivottables.htm


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Use Subtotal:


Subtotal() can produce a variety of calculation and, importantly, ignores
hidden cells.

First switch on autofilter. This will allow you display only a single sales
number (like 206).

Next at the bottom of your columns insert formulae like:
=SUBTOTAL(9,B1:B10000)
and copy across.

 

[Guest]

I would use the SUMIF function. In rows after the data put each salesman's
ID on a separate row in column A then put a formula in column B to calculate
the total for Product B. If the data goes from row 2 to row 9876 and the
sales person is on row 9882 the formula would be
=sumif($a$2:$a$9876,$a9882,b$2:b$9876). By using the $ character it is
possible to copy the formulae to other sales people & other products