Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for totaling column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for totaling column
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) "dbglass" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for totaling column
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. -- Gary's Student "dbglass" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for totaling column
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 "dbglass" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
how can i multiply two columns | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions |