Hi!
Yesterday you wanted them in descending order!
Peo gave you the *BEST* solution available. However, if
you *REALLY* want overly complex formulas to do something
that is otherwise pretty simple here they a
Assume your data is in the range A1:B18.
To return the names in ascending order based on totals:
=INDEX({"Jim","Rose","Bill","Bob"},MATCH(SMALL(SUM IF
($A$1:$A$18,{"Jim","Rose","Bill","Bob"},$B$1:$B$18 ),ROW
($A1)),SUMIF($A$1:$A$18,
{"Jim","Rose","Bill","Bob"},$B$1:$B$18),0))
Copy down.
In an adjacent cell enter this formula to get the totals
also in ascending order:
=SMALL(SUMIF($A$1:$A$18,
{"Jim","Rose","Bill","Bob"},$B$1:$B$18),ROW($A1 ))
Copy down.
I myself would use Peo's suggestion of an advanced filter
to copy unique records and then do a simple sumif followed
by a sort ascending.
Biff
-----Original Message-----
How do I write a formula that will add all the sales for
an individual,
return the name in one cell and the total sales in
another?
Example of what I will enter into Excel:
Jim $100.00
Jim $126.78
Jim $153.56
Rose $180.34
Rose $207.12
Rose $233.90
Rose $260.68
Rose $287.46
Bill $314.24
Bill $341.02
Bill $367.80
Bill $394.58
Bob $421.36
Bob $448.14
Bob $474.92
Bob $501.70
Bob $528.48
Bob $555.26
This is what I would like the results to look like:
Jim $380.34
Rose $1,169.50
Bill $1,417.64
Bob $2,929.86
Thanks for your help.
.
|