Results 1 to 5 of 5
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting cell contents by groups (2003)

    See attached workbook. It uses SUMPRODUCT to calculate the counts.

    (You could have zipped the workbook)
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Counting cell contents by groups (2003)

    Hi jaf90,

    See attached workbook. It uses an array formula to summarise the data.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting cell contents by groups (2003)

    I sometimes wonder if you are looking over my shoulder waiting for me to press the "Post It" button to reply straight away!
    Many thanks.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting cell contents by groups (2003)

    Edited by HansV to present data in table format.

    I will have many more Groups/Tests in the proper spreadsheet.

    Students are in groups, Red, Blue, Green and Yellow. Their group and names are listed in columns 1 & 2.
    Students sit exams (test1, test2, test3 etc). The exam name is shown in the top row.
    Against each student a mark (A, B, C or D) is entered in the column for the specific test.

    I need to count the number of As, Bs, Cs and Ds (there may be more scores at a later date) and place the totals for each group at the end of the spreadsheet.
    I tried COUNTIF and COUNTA, but I can find no way to force either to pays heed to the groups which each person is contained in.

    Any suggestions? Example follows - I couldn't shrink the file size to less than 100k, though there is very little in the spreadsheet;

    <table border=1><td>Group</td><td>Name</td><td>Test1</td><td>Test2</td><td>Test3</td><td>Test4</td><td>Test5</td><td>Test6</td><td>Test7</td><td>Test8</td><td>Test9</td><td>Test10</td><td>Red</td><td>Adam</td><td align=right>*</td><td>A</td><td>C</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>A</td><td>Red</td><td>Adrian</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>D</td><td>C</td><td align=right>*</td><td align=right>*</td><td>B</td><td align=right>*</td><td>A</td><td>Green</td><td>David</td><td align=right>*</td><td align=right>*</td><td>D</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>A</td><td align=right>*</td><td align=right>*</td><td>Red</td><td>Amy</td><td align=right>*</td><td align=right>*</td><td>A</td><td align=right>*</td><td align=right>*</td><td>B</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>Red</td><td>Amelia</td><td align=right>*</td><td align=right>*</td><td>C</td><td align=right>*</td><td align=right>*</td><td>C</td><td align=right>*</td><td align=right>*</td><td>C</td><td>C</td><td>Blue</td><td>Bob</td><td align=right>*</td><td>D</td><td align=right>*</td><td>A</td><td>D</td><td align=right>*</td><td align=right>*</td><td>B</td><td align=right>*</td><td>B</td><td>Blue</td><td>Brian</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>Red</td><td>Andrew</td><td>D</td><td>B</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>B</td><td align=right>*</td><td>Yellow</td><td>Cheryl</td><td>B</td><td>B</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>D</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>Yellow</td><td>Christopher</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>A</td><td align=right>*</td><td align=right>*</td><td>D</td><td align=right>*</td><td>Red</td><td>Amanda</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>D</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>A</td><td align=right>*</td><td align=right>*</td><td>Green</td><td>Davina</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>B</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>Green</td><td>Donna</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>B</td><td align=right>*</td><td align=right>*</td><td>B</td><td align=right>*</td><td>D</td><td>A</td><td>Yellow</td><td>Carol</td><td align=right>*</td><td align=right>*</td><td>A</td><td>A</td><td align=right>*</td><td>A</td><td>B</td><td align=right>*</td><td align=right>*</td><td align=right>*</td></table>
    <table border=1><td>Group</td><td>As</td><td>Bs</td><td>Cs</td><td>Ds</td><td>Red</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>Blue</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>Yellow</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td>Green</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=right>*</td></table>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting cell contents by groups (2003)

    Another option in additon to using SUMPRODUCT (which was also given) is to rearrange the data to allow the use of a pivot table.

    This has the advantage of allowing summary in other wayrs (eg grouping by people as well)

    Steve
    Attached Files Attached Files

Posting Permissions

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