+ Post New Thread
Results 1 to 14 of 14
Office Software Thread, excel adding problem in Technical; i have a spreadsheet (excel 2003) thats i got from sims, it's like a 3 a 3 a 0 a ...
  1. #1
    browolf's Avatar
    Join Date
    Jun 2005
    Location
    Mars
    Posts
    1,540
    Thank Post
    112
    Thanked 89 Times in 75 Posts
    Blog Entries
    47
    Rep Power
    41

    excel adding problem

    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

  2. #2

    sparkeh's Avatar
    Join Date
    May 2007
    Posts
    7,230
    Thank Post
    1,445
    Thanked 1,859 Times in 1,252 Posts
    Blog Entries
    22
    Rep Power
    559
    Attached is a spreadsheet that gives you a running total.
    Attached Files Attached Files
    Last edited by sparkeh; 27th May 2010 at 04:56 PM.

  3. Thanks to sparkeh from:

    browolf (27th May 2010)

  4. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Presuming your characters are in column A and numbers in column B and that there are 10 rows, it would simply be:

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

  5. Thanks to LosOjos from:

    browolf (27th May 2010)

  6. #4

    sparkeh's Avatar
    Join Date
    May 2007
    Posts
    7,230
    Thank Post
    1,445
    Thanked 1,859 Times in 1,252 Posts
    Blog Entries
    22
    Rep Power
    559
    Quote Originally Posted by LosOjos View Post
    Presuming your characters are in column A and numbers in column B and that there are 10 rows, it would simply be:

    Code:
    =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

  7. Thanks to sparkeh from:

    browolf (27th May 2010)

  8. #5
    browolf's Avatar
    Join Date
    Jun 2005
    Location
    Mars
    Posts
    1,540
    Thank Post
    112
    Thanked 89 Times in 75 Posts
    Blog Entries
    47
    Rep Power
    41
    ah it's not really that simple.

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

  9. #6

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by browolf View Post
    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
    Last edited by LosOjos; 27th May 2010 at 05:41 PM.

  10. Thanks to LosOjos from:

    browolf (27th May 2010)

  11. #7
    browolf's Avatar
    Join Date
    Jun 2005
    Location
    Mars
    Posts
    1,540
    Thank Post
    112
    Thanked 89 Times in 75 Posts
    Blog Entries
    47
    Rep Power
    41
    Quote Originally Posted by LosOjos View Post
    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...?

  12. #8

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,405
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    169
    I think what you need is a pivot table

  13. #9

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    That's actually a much simpler way of doing it!

  14. #10

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,159
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    125
    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 :-)

  15. #11

    Join Date
    Jun 2007
    Location
    Sheppey, Kent
    Posts
    9
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi,

    How about: from C1,

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

    and copy down? (Drag)


    Gaz
    Last edited by gskelton; 3rd June 2010 at 09:46 AM.

  16. #12

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    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.

  17. #13

    Join Date
    Jan 2009
    Posts
    192
    Thank Post
    17
    Thanked 27 Times in 22 Posts
    Rep Power
    16
    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.

  18. #14

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by Ketlane View Post
    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.



SHARE:
+ Post New Thread

Similar Threads

  1. Problem adding computers to the domain
    By zag in forum Windows
    Replies: 3
    Last Post: 25th May 2010, 12:58 PM
  2. Replies: 0
    Last Post: 24th May 2010, 10:01 AM
  3. [MS Office - 2003] Adding sound to an Excel spreadsheet
    By leco in forum Office Software
    Replies: 2
    Last Post: 22nd February 2010, 08:59 PM
  4. Excel sum problem
    By wesleyw in forum How do you do....it?
    Replies: 8
    Last Post: 29th August 2009, 12:23 PM
  5. Excel Problem
    By TechSupp in forum General Chat
    Replies: 0
    Last Post: 2nd November 2007, 11:08 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •