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

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

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

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

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

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

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