I'm always coming up against this!

M

Michelle Tucker

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!
 
A

Andibevan

Try SUMPRODUCT:-

=Sumproduct((RANGE_1="Criteria1")*(Range_2="Criteria2"))

Range1 and Range2 need to have the same number of rows in

Regards

Andy

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!
 
B

Bernie Deitrick

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
 
D

Duke Carey

You could also create a Pivot Table. Perfect for this type of report

Data>Pivot Table... & follow the wizard
 
D

Dave Peterson

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/assistance/2002/articles/xlconPT101.aspx
 
M

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!
 
Top