Sum calculation based on matching criteria

[Patrick1471]

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.

[TMS]

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

[royUK]

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

[royUK]

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.

[Patrick1471]

@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

[Patrick1471]

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

[TMS]

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

[Patrick1471]

@TM Shucks

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

Patrick.

[Blake 7]

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

[Patrick1471]

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.

[royUK]

No attachment, try zipping it