Function Assistance

I am really battling and hoping someone will be able to assist me with this query. I am using MS Excel 2003. I need to return the answer to the formula/funciton on sheet 1. The data is sitting is sheet 2.
I need excel to search column A in sheet 2 based on criteria set and then only if it is true add all values in column B pertaining to the criteria set on the search in column A. And then average that sum by the number of times the criteria is returned.
1. So in column A I have a list of people, in some cases this will be blank, so blank needs to serve as one of the people. A persons name will be in the list numerous times.
2. I need to add all the values in column B (hours spent) pertaining to a specific person.
3. I then need to average that total by the number of incidences their name appears. Thereby getting an average of hours spent per incident.

Like I said earlier there are "BLANK" spaces in the list of names, these essentially become another person on the list.

Your assistance will be greatly appreciated.

Hi royUK,

Thank you for the response. I have attached the spreadsheet I am working on. I have included comments on the first sheet, saying what I require. Please note that Sheet 3 "March' can be disregarded as it is not important in the context of this query.

Thank you.

Well, let me try to help, although I'm not sure I understood well: in the beginning you mentuion criteria to search column A, and then it appears to be only the name of the person? So there is no other criteria, only name in column A, and some numeric value in column B?

If so, and if you have list of all names, look at the attachment.

There is sheet "Names", where are your raw data, i.e., unsorted names.

In sheet "Output", there is calculation you need, but only IF you can provide name you want to calculate data for; I mean, you need to know all names which appear in column A, I can't find way now to extract them. So, you just enter names in appropriate fields at sheet "output" (in this example, I had 7 names, and blank cell you mentioned, so those are cells A6 through A13; I coloured them in yellow), and also you need to enter first and last cell you have data in sheet "Names" in cells B2 and B3 (also coloured in yellow); in our example, I have names in cells A2 through A18, so I put numbers 2 and 18 in those two cells I mentioned.

And that's all - in respective columns, you get sum of all numbers related to names (and also to blank cell, which is the last row), number of appearances of each name, and average, which is obviosly calculated dividing those two numbers.

Of course, you don't need to have all names for formulas to work - if you want to see summary only for one name, enter that name, and you'll get its data.

Also, pay attention if the "blank" cells are really blank, or have space inside - the results will be different!

Cells C2 through D3 are auxiliarry only, to avoid long formulas in main table; that way, you can easily add more names, just by inserting new rows and copying formulas.

I hope this suits your needs.

Whooops, you two posted in the meantime, while I was working on this...

I had a look at your workbook, and it looks pretty the same what I described, so it should work - I see you have only 10 names there, it was not difficult to copy them accross the table; look at the attachment now.

I have changed name of your sheet "Formula for data" to "Names", as it was easier to me than to change name in formulas; hope you don't mind.

Thank you for your assistance. Much appreciated.