Adding multiple cells, return specific values
 

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.

Hi Jim

couple of options -
1) use data / subtotals to automatically generate this (you can then use the
outline levels to hide the details allowing you to print the summary)
(ensure that you've sorted by NAME first)
2) use a pivot table - data / pivot table and pivot chart report ... to use,
place your cursor in one cell of the data, choose data / pivot table & pivot
chart report, choose NEXT, check the range and choose NEXT, ensure in a new
worksheet and then click the LAYOUT button (before clicking FINISH)- drag
the names to the "row" section, drag the values into the "data" section, now
click OK & Finish and you should have what you want
3) use a formula
=SUMIF($A$1:$A$10,A15,$B$1:$B$10)
where A1:A10 is the range containing the names
A15 is Jim's name (with the formula being entered into B15)
and B1:B10 is the range to sum
if you have Jim's name in A15, Rose's name in A16 etc, once you've entered
the formula in B15 for Jim you can fill down for the other people

Cheers
JulieD


"Jim" wrote in 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.

D E
1 Jim $380.34
2 Rose $1,169.50
3 Bill $1,417.64
4 Bob $2,929.86

E1: =SUMIF(A:A,D1,B:B)

--
Mike

"Jim" wrote in message ...

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.


.

Hi
as stated in your other post: Use a pivot table (this is what they were
made for). See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2...simple-pivot-t
able/trackback/


--
Regards
Frank Kabel
Frankfurt, Germany

"Jim" schrieb im Newsbeitrag
...
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.