Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I'm always coming up against this!
I always seem to have this problem, and any solutions I have I don't truly
understand! I have a list of products sold in one column, and in the next, the name of the salesperson who sold it. What I want to do is count up the amount of particular products sold by each person. I understand countif and sumif, but it seems to be done with a combintation of INDEX and MATCH, which I don't get. What is the easiest way to do this as it is something I always seem to need to do, but then chicken out! -- Michelle Tucker |
#2
|
|||
|
|||
Try SUMPRODUCT:-
=Sumproduct((RANGE_1="Criteria1")*(Range_2="Criter ia2")) Range1 and Range2 need to have the same number of rows in Regards Andy "Michelle Tucker" wrote in message ... I always seem to have this problem, and any solutions I have I don't truly understand! I have a list of products sold in one column, and in the next, the name of the salesperson who sold it. What I want to do is count up the amount of particular products sold by each person. I understand countif and sumif, but it seems to be done with a combintation of INDEX and MATCH, which I don't get. What is the easiest way to do this as it is something I always seem to need to do, but then chicken out! -- Michelle Tucker |
#3
|
|||
|
|||
Michelle,
Learn how to use Pivot Tables, and your life will be much easier. Select your data table, the use Data / Pivot table... then click Finish. Drag the 'Product' button (its actual name will depend on your column heading) to the row fields area, then do the same for "Sales Person", and then drag either of those to the data field as well. You will then get a count of how many of each product each salesman sold, with no formulas involved. HTH, Bernie MS Excel MVP "Michelle Tucker" wrote in message ... I always seem to have this problem, and any solutions I have I don't truly understand! I have a list of products sold in one column, and in the next, the name of the salesperson who sold it. What I want to do is count up the amount of particular products sold by each person. I understand countif and sumif, but it seems to be done with a combintation of INDEX and MATCH, which I don't get. What is the easiest way to do this as it is something I always seem to need to do, but then chicken out! -- Michelle Tucker |
#4
|
|||
|
|||
You could also create a Pivot Table. Perfect for this type of report
DataPivot Table... & follow the wizard "Michelle Tucker" wrote: I always seem to have this problem, and any solutions I have I don't truly understand! I have a list of products sold in one column, and in the next, the name of the salesperson who sold it. What I want to do is count up the amount of particular products sold by each person. I understand countif and sumif, but it seems to be done with a combintation of INDEX and MATCH, which I don't get. What is the easiest way to do this as it is something I always seem to need to do, but then chicken out! -- Michelle Tucker |
#5
|
|||
|
|||
If you want to read more about the pivottable stuff, you may want to look at
some links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Michelle Tucker wrote: I always seem to have this problem, and any solutions I have I don't truly understand! I have a list of products sold in one column, and in the next, the name of the salesperson who sold it. What I want to do is count up the amount of particular products sold by each person. I understand countif and sumif, but it seems to be done with a combintation of INDEX and MATCH, which I don't get. What is the easiest way to do this as it is something I always seem to need to do, but then chicken out! -- Michelle Tucker -- Dave Peterson |
#6
|
|||
|
|||
Ditto that, Michelle. A pivot table is awkward the first hour or so, after
that you cant imagine how you managed without. http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm I'd give a money back guarantee if this wasn't for free already. HTH. Best wishes Harald "Michelle Tucker" skrev i melding ... I always seem to have this problem, and any solutions I have I don't truly understand! I have a list of products sold in one column, and in the next, the name of the salesperson who sold it. What I want to do is count up the amount of particular products sold by each person. I understand countif and sumif, but it seems to be done with a combintation of INDEX and MATCH, which I don't get. What is the easiest way to do this as it is something I always seem to need to do, but then chicken out! -- Michelle Tucker |
#7
|
|||
|
|||
You are all right! I've just done the training online for pivot tables, and I
don't know how I survived without them before! Thanks! -- Michelle Tucker "Bernie Deitrick" wrote: Michelle, Learn how to use Pivot Tables, and your life will be much easier. Select your data table, the use Data / Pivot table... then click Finish. Drag the 'Product' button (its actual name will depend on your column heading) to the row fields area, then do the same for "Sales Person", and then drag either of those to the data field as well. You will then get a count of how many of each product each salesman sold, with no formulas involved. HTH, Bernie MS Excel MVP "Michelle Tucker" wrote in message ... I always seem to have this problem, and any solutions I have I don't truly understand! I have a list of products sold in one column, and in the next, the name of the salesperson who sold it. What I want to do is count up the amount of particular products sold by each person. I understand countif and sumif, but it seems to be done with a combintation of INDEX and MATCH, which I don't get. What is the easiest way to do this as it is something I always seem to need to do, but then chicken out! -- Michelle Tucker |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find which column or row the Min() value is coming from | Excel Discussion (Misc queries) | |||
can anyone help with times not coming across correctly in pivot t. | Excel Worksheet Functions | |||
can anyone help with times not coming across correctly in pivot t. | Excel Worksheet Functions | |||
why are nested subtotals coming out below outer subtotals? | Excel Worksheet Functions | |||
Coming up with #N/A | Excel Worksheet Functions |