+ Reply to Thread
Results 1 to 11 of 11

Sum calculation based on matching criteria

  1. #1
    Registered User
    Join Date
    12-06-2010
    Location
    Norwich, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Sum calculation based on matching criteria

    Hi

    I need to sum all the values in one column that have the same corresponding name variable in another column. I then need to calculate the mean value of the sum values sharing a common name.

    Example.

    35-House-46
    67-House-46
    36-House-46
    86-Car-68.5
    46-Car-68.5
    86-Car-68.5
    56-Car-68.5
    58-Bike-62.6
    43-Bike-62.6
    87-Bike-62.6

    Thanks in advance for any help.

    Patrick.
    Attached Files Attached Files
    Last edited by Patrick1471; 12-08-2010 at 11:00 AM. Reason: Sample data sheet added

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,975

    Re: Sum calculation based on matching criteria

    AverageIf Method [Excel 2007 Developer Reference]
    Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx


    Regards
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Sum calculation based on matching criteria

    You might be able to use a PivotTable, but it would help to see a sample workbook
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Sum calculation based on matching criteria

    This is a duplicate of your other posts, I strongly recommend that you actually read the Forum Rules that you agreed to when you joined.

  5. #5
    Registered User
    Join Date
    12-06-2010
    Location
    Norwich, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Sum calculation based on matching criteria

    @TMShuks

    Thanks for your suggestion. Could you please elaborate on how I can tell excel to calculate the mean for a range of cells sharing the same name variable, I need excel to separate all names automatically as I am working with such a large data set.

    Patrick

  6. #6
    Registered User
    Join Date
    12-06-2010
    Location
    Norwich, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Sum calculation based on matching criteria

    Quote Originally Posted by royUK View Post
    This is a duplicate of your other posts, I strongly recommend that you actually read the Forum Rules that you agreed to when you joined.
    Thanks for your suggestion

    Sorry If I have broken another rule, I thought my other posts had been closed by moderators and so I had to make a new post.

    I will now fully consult the rules.

    Regards

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,975

    Re: Sum calculation based on matching criteria

    I'm not sure I can elaborate without an example workbook.

    Another link: http://spreadsheets.about.com/od/exc..._averageif.htm

    It's even hard to explain without an example ... you would start, I think, by extracting a list of unique values that you want to get the average of their corresponding values. This could be done using an Advanced Filter or by using a formula.

    A sample workbook would give a better idea of what you are working with and what options are appropriate.

    Regards

  8. #8
    Registered User
    Join Date
    12-06-2010
    Location
    Norwich, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Sum calculation based on matching criteria

    @TM Shucks

    I can email you a sample of my workbook if this might help you see what i am trying to do.

    Patrick.

  9. #9
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,373

    Re: Sum calculation based on matching criteria

    Patrick - upload it to the Forum > Go Advanced > Manage Attachments. then more people can have a look and you get more chances of assistance!
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/

  10. #10
    Registered User
    Join Date
    12-06-2010
    Location
    Norwich, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Sum calculation based on matching criteria

    Sample data sheet now up. For every different STward_code I need to find the corresponding IMD score. Note that most STward_code's occur more than once so I need to find the average corresponding IMD score.

    Hope this helps.

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Sum calculation based on matching criteria

    No attachment, try zipping it

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1