microsoft excel?

Question

hi, i am working for a trucking company, we have a spreadsheet on microsoft excel.
we have like e.g. 200 companies that we deal with. On the spread sheet that we have, A company like A&G, for example, might be on the spreadsheet like 6 times because thats how many times we have worked with them. so even though we have worked with like 200 companies, on my spreadsheet i have like 4 or 5 hundred because we might of worked with a company more than once. And the list might look like this

A&G
GLOBAL
A&R
A&G
so A&G is there more than once but scattered throughout the sheet.
Now my question is, i am working on a new sheet.
on my new sheet, i want to have each companys name and the total amount of money for that company this year. So instead of clicking CTRL F and going one by one adding up the total profit and then pasting it onto my new spreadsheet, IS THERE AN EASIER WAY to get the total of each company without going one by one.

Answer 1

The guys are answerring very good here
But may be you need another solution
I always used to do it using either
SUBTOTAL function
or SUM + IF functions
you will need to mail me a sample of the file you are talking about
and I can then reply you with the same file solved

It is too diffecult to show you here how

you can get my e-mail in my profile

Enjoy my profile, I am the VBAXLMan here

Answer 2

oh man, i'm sorry, your question's made me SO GLAD i haven' t had to have anything to do with excel in a few months!!! sorry, i just am...

however, what i'd probably do (and it's probably wrong) is use a filter. first - make a copy of the file, and save it under some other name. then - in the copy -

1) create a list of the whole list. you do this by selecting all the relevant cells. go to 'data', and select 'list'.

2) use the arrows to filter by name

3) do your sums, then delete now unnecessary entries

aw, now i'm mad i went back there.

Answer 3

assume data is in Sheet1
column A: Companies Name
data input, cell: B2:M10

Sheet2
A1:A200 => list of 200 companies
(use Sort/Filter function then copy paste to Sheet2!A1:A200)

in cell B1 put this function, and copy up to cell B200

=SUMPRODUCT((Sheet1!A$2
:A$10=A1)*
(Sheet1!B$2:M$10))

Good luck.

Answer 4

Depending on how your spreadsheet is set up, I recommend you looking into the VLOOKUP and HLOOKUP commands.

Answer 5

if your sheet is setup properly...you could use a pivot table, or autofilter would work