+ Post New Thread
Results 1 to 9 of 9
How do you do....it? Thread, Excel sum problem in Technical; I need to workout how many pupils have say taken French (column b) and have attained an a grade (column ...
  1. #1
    wesleyw's Avatar
    Join Date
    Dec 2005
    Location
    Kingswinford
    Posts
    2,205
    Thank Post
    223
    Thanked 50 Times in 44 Posts
    Blog Entries
    1
    Rep Power
    30

    Excel sum problem

    I need to workout how many pupils have say taken French (column b) and have attained an a grade (column c)

    something like =sum(($b3:$b58=condition1)*($c3:$c58=condition2))

    but I can't remember the exact way of doing this I believe there is also when using the formula woizard some key to press as well but it's been 12 months and I can't remember what I did last time?

    Wes

  2. #2
    Norphy's Avatar
    Join Date
    Jan 2006
    Location
    Harpenden
    Posts
    2,236
    Thank Post
    50
    Thanked 273 Times in 211 Posts
    Blog Entries
    6
    Rep Power
    108

    Re: Excel sum problem

    sumif?

    From Excel help..

    SUMIF

    Adds the cells specified by a given criteria.

    Syntax

    SUMIF(range,criteria,sum_range)

    Range is the range of cells you want evaluated.

    Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".

    Sum_range are the actual cells to sum.

    Remarks

    The cells in sum_range are summed only if their corresponding cells in range match the criteria.

    If sum_range is omitted, the cells in range are summed.

    Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function. To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF function.

  3. #3


    Join Date
    Jan 2006
    Posts
    8,202
    Thank Post
    442
    Thanked 1,032 Times in 812 Posts
    Rep Power
    339

    Re: Excel sum problem

    awk ?

    Code:
    awk '{print $2 $3}'  file.csv  | grep -i 'french' | grep -i 'c' | wc -l

  4. #4

    Join Date
    Feb 2006
    Posts
    1,187
    Thank Post
    0
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Re: Excel sum problem

    What?! Formulas? Exports? scripts?
    Too much like hard work.

    Why not just use the automatic filter (Data menu) on the table.

    Set filter to match subject French and set the filter on column b to match.
    It should tell you the number of records that match on the status line.

    @Norphy you should use COUNTIF because I think SUMIF is for numbers.

    HTH

  5. #5

    Gatt's Avatar
    Join Date
    Jan 2006
    Posts
    6,658
    Thank Post
    858
    Thanked 646 Times in 429 Posts
    Rep Power
    498

    Re: Excel sum problem

    try the COUNTIFS() function

    Code:
    =COUNTIFS(Range1,criteria1,Range2,criteria2)
    eg:
    Code:
    =COUNTIFS($b3:$b58,"French",$c3:$c58,"A")

  6. #6

    Join Date
    Aug 2009
    Location
    Netherlands
    Posts
    8
    Thank Post
    2
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Why not use a Pivot Table? They are just a few mouse clicks.
    Assuming your table looks something like this...

    Name Subject Grade
    Name1 Maths c
    Name2 Maths a
    Name3 Maths b
    Name4 Maths d
    Name5 Maths a
    Name6 French a*
    Name7 French b
    Name8 French b
    Name9 French c
    Name10 French c
    Name11 English b
    Name12 English a*

    If you create a Pivot Table [Data > Pivot Table and Pivot Chart Report... in 2003] or [Insert Tab in 2007].

    Row = Subject
    Column = Grade
    Data = Count of Name (I find Admission Number more reliably unique for counting)

    You then get... [tried to paste it here but the format went horribly wrong]...what you want.


    If you use SIMS you can automate this from SIMS report. I can dig out some instructions for this if you wish.

    "One hundred idiots make idiotic plans and carry them out. All but one justly fail. The hundredth idiot, whose plan succeeded through pure luck, is immediately convinced he's a genius." Iain M Banks

  7. #7


    Join Date
    Feb 2007
    Location
    Northamptonshire
    Posts
    4,687
    Thank Post
    352
    Thanked 794 Times in 714 Posts
    Rep Power
    346
    Hey Adrian,

    Welcome to Edugeek.

    Looking at the last time this thread was replied to (3 years ago) I'd think it was resolved by now

  8. Thanks to kmount from:

    AdrianSpencer (29th August 2009)

  9. #8

    Join Date
    Aug 2009
    Location
    Netherlands
    Posts
    8
    Thank Post
    2
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Ooops. I'll check the dates in future.
    "One hundred idiots make idiotic plans and carry them out. All but one justly fail. The hundredth idiot, whose plan succeeded through pure luck, is immediately convinced he's a genius." Iain M Banks

  10. Thanks to AdrianSpencer from:

    kmount (29th August 2009)

  11. #9


    Join Date
    Feb 2007
    Location
    Northamptonshire
    Posts
    4,687
    Thank Post
    352
    Thanked 794 Times in 714 Posts
    Rep Power
    346
    hehe, welcome nonetheless, it's always good to see people keen to help

SHARE:
+ Post New Thread

Similar Threads

  1. Excel Formula Copy Problem
    By sqdge in forum Office Software
    Replies: 26
    Last Post: 10th February 2013, 06:18 AM
  2. Strange Excel Problem
    By Jon in forum Windows
    Replies: 14
    Last Post: 14th July 2011, 04:55 PM
  3. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 04:56 PM
  4. Excel Problem
    By TechSupp in forum General Chat
    Replies: 0
    Last Post: 2nd November 2007, 10:08 PM
  5. Bizzare Excel Problem
    By Norphy in forum Windows
    Replies: 3
    Last Post: 17th January 2006, 10:19 AM

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
  •