excel adding problem

[browolf]

i have a spreadsheet (excel 2003) thats i got from sims, it's like

 

a 3

a 3

a 0

a 2

b 10

b 0

b 0

b 3

 

etc

 

how do i make it so i have a list that's

a {sum of all the a's}

b {sum of all the b's}

 

etc

[sparkeh]

Attached is a spreadsheet that gives you a running total.

browolf.xlsx

Edited May 27, 2010 by sparkeh

[LosOjos]

Presuming your characters are in column A and numbers in column B and that there are 10 rows, it would simply be:

 

=SUMIF($A$1:$A$10,"a",$B$1:$B$10)
=SUMIF($A$1:$A$10,"b",$B$1:$B$10)
etc.

[sparkeh]

Presuming your characters are in column A and numbers in column B and that there are 10 rows, it would simply be:

 

=SUMIF($A$1:$A$10,"a",$B$1:$B$10)
=SUMIF($A$1:$A$10,"b",$B$1:$B$10)
etc.

 

Though that makes more sense

[browolf]

ah it's not really that simple.

 

I have a list of 1641 items and it's unknown how many individual ones there are.

[LosOjos]

ah it's not really that simple.

 

I have a list of 1641 items and it's unknown how many individual ones there are.

 

Please don't take offense at this if I have the wrong impression, but I'm guessing you don't use Excel a lot, so please excuse me if you feel I'm being too simple in my instructions.

 

The SUMIF formula takes 3 arguments:

1. The range of cells to search inside for the value specified in the second argument
   
2. The value you're searching for (in your case, this is the letters, which I presume are grades)
   
3. The range to add up when the search criteria is found; this should be the same amount of rows as the first range, but aimed at the column containing the values to add.
   

 

When you start to enter the formula, after you type the opening bracket, if you click on the 'fx' button to the left of the formula bar, you will get a popup to help you select the relevant values.

 

Your first argument is a range, so if you click the little box with an arrow inside it to the right of the text box, Excel will allow you to select a range. The quickest way to do this is to click on the first value in your column, then whilst holding shift, press end followed by down; this will highlight everything up to the last item in that column. Click the button to the right of the text box to return to your helper popup.

 

The next is your search criteria; the grade; for a text value, enclose it in quotes (e.g. "a"), otherwise Excel will look for a named range and return an error.

 

The final column is the list of values to add if the search criteria is true, repeat the steps for the first argument, but this time selecting the column with the values inside it.

 

 

Again, apologies if that was too simplistic, I may have misunderstood what you meant and in fact you might be an Excel guru!

 

 

EDIT: One thing Microsoft do well is write support documents! SUMIF - Excel - Microsoft Office Online

Edited May 27, 2010 by LosOjos

[browolf]

Please don't take offense at this if I have the wrong impression, but I'm guessing you don't use Excel a lot, so please excuse me if you feel I'm being too simple in my instructions.

 

Again, apologies if that was too simplistic, I may have misunderstood what you meant and in fact you might be an Excel guru!

 

 

 

sorry sorry it's my fault, because When I said in the OP "it's like...." that was me thinking it would be easier if I simplified the problem... ie the letters aren't letters....for the case of argument they could be subjects. whilst there is a finite amount of uniques, I was hoping there was a way excel could handle the fact that I don't know what they are in advance.

 

I was wondering if there's was a few buttons I could press but if it comes to having to write a script in vba i might as well just have the data in .csv and write a dos vbscript. I'm thinking I'm going to have to script it...?

[CESIL]

I think what you need is a pivot table

[LosOjos]

That's actually a much simpler way of doing it!

[srochford]

Definitely want a pivot table (also known as a cross tab in other packages)

Put a column heading at the top of the subject and mark column, highlight your data and do insert pivot table

Put the subject as row headings and then add mark - Excel will guess you want to sum the mark (but there are options to average it or count it, for example)

 

If you've never used pivot tables before then to take a bit of time to get to grips with them - they are brilliant for doing all sorts of analysis and just totally sexy :-)

[gskelton]

Hi,

 

How about: from C1,

 

=SUMIF($A$1:$A$10,A1,$B$1:$B$10)

 

and copy down? (Drag)

 

 

Gaz

Edited June 3, 2010 by gskelton

[vikpaw]

pivot tables are both sexy and scary at the same time.

 

if you order the list first, a quick way without the complications is to use the Sub-Totals function from Data menu. You just need to have column labels at top, and for every change in column A it will give you a sub-total. It's kind of like a really, really simple pivot table, but for simple data is quick and also will group the data for you so you can minimise the raw data at the touch of a button and just see the list of uniques with values.

[Ketlane]

Isn't COUNTIF the functions we need? It takes two arguments, range and criteria, so =COUNTIF(A":A2000,"a") would count the a cells for you. This is what I use for counting grades etc.

[LosOjos]

Isn't COUNTIF the functions we need? It takes two arguments, range and criteria, so =COUNTIF(A":A2000,"a") would count the a cells for you. This is what I use for counting grades etc.

 

COUNTIF will indeed count the occurences of grades, but browolf wanted the sum of the second column based on the value in the first column, that's why I suggested SUMIF. CESIL then made the very good point that a pivot table would do it, and it'd be much nicer looking that way too.