Sum numbers based on specified criteria

[R.Miller]

If I have a spreadsheet with various data in a column (perhaps sales rep
names) and I want to sum all values associated with that sales rep, how do I
do it.

For example, if I have hundreds of clients assigned to either sales rep A,
B, C, D or E and I want to know the dollar value of sales for each sales rep,
what is the proper formula? I would separate totals for the sales dollars
where sales rep = A, sales rep = B, on so on.

 

[Don Guillett]

Have a look in the help index for COUNTIF & SUMIF

 

[krcowen]

The proper formula would probably be either:

Sumit
Sum (probably an array sum)
Sumproduct
Dsum
or maybe a Pivot Table or SubTotals

Check the Help file on each and see which best meets your needs.

Good luck.

Ken

 

[Rick Rothstein \(MVP - VB\)]

names) and I want to sum all values associated with that sales rep, how do

I
do it.

For example, if I have hundreds of clients assigned to either sales rep A,
B, C, D or E and I want to know the dollar value of sales for each sales
rep,
what is the proper formula? I would separate totals for the sales dollars
where sales rep = A, sales rep = B, on so on.

Assuming for example purposes that your sales rep names are in column A and
the sales figure is in column B...

=SUMPRODUCT((A1:A13="A")*(B1:B13))

where you would replace the A in quote marks with the sales rep's name or ID
text (and, of course, expand the range to suit your data)

Rick

 

[Toppers]

=SUMPRODUCT(--($A$2:$A$100)="Sales rep A"),$B$2:$B$100)

B = Sales values

Put sales rep in cells (say C2 to C10) and use:


=SUMPRODUCT(--($A$2:$A$100)=C2),$B$2:$B$100)

and copy down

OR look at SUMIF function

HTH

 

[FSt1]

hi
=sumif()
you would need a sumif formula for each sales rep.
assuming sales are in column A and the rep names are in column b.....
=sumif(B1:B5000,"RepName", A1:A50000)
you can stack the sumif formulas in a summary column then total the summary
column for a grand total.

regards
FSt1