Automatical summation of entries ?!

[Guest]

Dear ALL,

in my spreadsheet i have a number of entries such as:

Andy Los Angeles -$100
Andy Los Angeles -$78
Andy Los Angeles $300
Andy Los Angeles $65
Troy New York -$99
Troy Los Angeles -$13
Troy Los Angeles $235
Mike New Orleans $44
Mike New Orleans $432

is there any way (maybe by means of VB) to modify the entries so that it
gives the summary for everybody. for istance,

Andy Los Angeles $187 (which is 300-100-78)
Troy New York $123
Mike New Orleans $476

thank you in advance

 

[Cathy]

Try: Tools/Wizard/Conditional Sum
It will give you subtotals for each person, then you can
hide the detail, leaving you with the report you are
looking for. (To the right you will find +/- signs that
you can use to hide the detail.)

Cathy

 

[duane]

if your input is in say a1:a10, b1:b10, c1:c10

and if you have "andy" in a15, "los angeles" in b15

put this in c15 to get total for andy / los angeles

=sumproduct(($a$1:$a$10=a15)*($b$1:$b$10=b15)*($c$1:$c$10))

copy down (putting names and cities in columns a and b below and & lo
angeles

 

[Guest]

Dear Cathy,

after summation I have the data will be automatically pulled by local
programm and it will not be able to choose specific (unhidden) cells from the
file.
that is why I cannot use the way you described....that is why i need
probably to apply Visual Basic to form new sheet that will summarize the
results

I will greatly appreciate your help in the matter

 

[Guest]

You can also use: Data/Subtotals (choose the solution
that works best for you and provides the look you want.)

 

[Frank Stone]

hi,
you might have to tinker with the ranges but it works
Sub sumdel()

Dim nam As Range
Dim nam1 As Range
Dim qty As Range
Dim qty1 As Range

Set nam = Range("A2")
Set qty = Range("c2")
Do While Not IsEmpty(nam)
Set nam1 = nam.Offset(1, 0)
Set qty1 = qty.Offset(1, 0)
If nam.Value = nam1.Value Then
qty.Value = qty.Value + qty1.Value
qty1.EntireRow.Delete
Else
Set nam = nam1
Set qty = qty1
End If
nam.Select
Loop
End Sub
enjoy
frank