how do I sort and add all at once?

R

rivethead

Each month I get an Excel spreadsheet with 2 columns. The first colum
is a list of servers(many of them repeated) and the second colum
states how much disk space was allocated to the corresponding server.
I'd like to find an easy way to add all the disk space for eac
individual server. Here is how it looks using a : as a column gri
line.

server1:40
server1:20
server1:45
server1:300
server1:18
server2:65
server2:40
server2:11
server2:345

What is the easiest way to add together each of server1's disk spac
(server 2, etc..) for a grand total for each server?

I'm using Excel ver. 2002.

Thanks
 
R

rivethead

Thank you for the speedy reply JE McGimpsey. My browser locked u
during my post so I re-posted :( .

I'm looking for an alternative to clicking on an empty cell, the
hitting the 'sum' button and pressing <enter>. I figured that Exce
would have something built in that would find matching entries in col
and add together the corresponding numbers in col 2.

Thanks again
 
K

Ken Wright

SUMIF

No need to sort the data. Put the formula at the top and refer to a big range
below, and you can just paste the new data in and not have to do anything. You
can also use a dynamic range, eg:-

With 'Server1' in cell A1 and 'Server2' in cell A2

In cell B1 put

=SUMIF(OFFSET($A$3,,,COUNTA(A:A)-2),A1,OFFSET($B$3,,,COUNTA(A:A)-2))

In cell B2 put

=SUMIF(OFFSET($A$3,,,COUNTA(A:A)-2),A2,OFFSET($B$3,,,COUNTA(A:A)-2))

Stick a border or something under A2:B2 to show it is a total, and then paste in
your data from cell A3 down. Totals will all automatically be calculated, and
ranges will adjust aitomatiocally to accommodate more/less records - CAVEAT - No
blanks allowed in your data in Col A, or the COUNTA function will not work
properly. Also, text in A1 and A2, ie the Server1/2 bit, MUST be exactly how it
appears in the ranges below.
 
F

Frank Kabel

And just as an addition:
If you have blank rows in between (but otherwise only numbers) you may
use
MATCH(9.99999999999999E300,A:A)

B1:
=SUMIF(OFFSET($A$3,,,MATCH(9.99999999999999E300,A:A)-2),A1,OFFSET($B$3,
,,MATCH(9.99999999999999E300,A:A)-2))

B2:
=SUMIF(OFFSET($A$3,,,MATCH(9.99999999999999E300,A:A)-2),A2,OFFSET($B$3,
,,MATCH(9.99999999999999E300,A:A)-2))




--
Regards
Frank Kabel
Frankfurt, Germany

Ken said:
SUMIF

No need to sort the data. Put the formula at the top and refer to a
big range below, and you can just paste the new data in and not have
to do anything. You can also use a dynamic range, eg:-

With 'Server1' in cell A1 and 'Server2' in cell A2

In cell B1 put

=SUMIF(OFFSET($A$3,,,COUNTA(A:A)-2),A1,OFFSET($B$3,,,COUNTA(A:A)-2))

In cell B2 put

=SUMIF(OFFSET($A$3,,,COUNTA(A:A)-2),A2,OFFSET($B$3,,,COUNTA(A:A)-2))

Stick a border or something under A2:B2 to show it is a total, and
then paste in your data from cell A3 down. Totals will all
automatically be calculated, and ranges will adjust aitomatiocally to
accommodate more/less records - CAVEAT - No blanks allowed in your
data in Col A, or the COUNTA function will not work properly. Also,
text in A1 and A2, ie the Server1/2 bit, MUST be exactly how it
appears in the ranges below.
 
J

JE McGimpsey

Seems to me that if you're gonna use a ghastly number like that, you
might as well include the last 7 possible orders of magnitude...

9.99999999999999E307

<g>
 
D

Dave Peterson

Or if you're not using columns A and B for anything else:

=SUMIF(A:A,"server1",B:B)

(put the formula in a cell not in column A or B.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top