Lookup function help needed

The problem:

The cell in column M and N would need to lookup the name found in the A column and then find wherever they are used in sheet4 (which I mentioned
acted as the array), then to the right of the cell where the name is found in sheet4, the score will be present. For some members, they
have multiple scores (meaning they have been entered numerous times on the sheet. This is intended). I would need for all the scores for the specific member to be added up and added under the "Total Score"(N) column in their
respective cell on the "testdb" sheet. As for the average score (M) column, I would like for their averages to be entered there.

I have tried so much but this seems beyond me.
Thank you for any assistance.

I have attached a file called EFFile

(ExcelForums EFFile.xlsm).

See attached file .Formula in B2 is dragged across.
ARRAY formulas are used

How to enter ARRAY formula.
Paste the formula
Press F2
Press Ctrl+Shift+Enter Keys together.

You did work I did not even ask for...are you God?
Thank you very much but I wont yet mark it as solved...I may need more help.
Is that ok?

Another issue I found is when I entered in "Kareen" and "7" in sheet4's cells F22 and G22 respectively it did not update the results in the "testdb" sheet.

bump.
Anyone?

Originally Posted by ChemistB

On Sheet4, will a name appear more than once in a single column?

It will appear more than once in a single column, yes.
And will more than likely appear in multiple columns too.

Originally Posted by ChemistB

I just did it on the uploaded sheet.
I selected Col E from sheet4, then Data>Text to Columns> Finish
the numbers should jump from the left to the right.

When I tried to apply that to the A column on testdb sheet, the values within the cells disappeared.
However when I tried it in an empty cell, for example E5, and entered in a value...it did everything correctly in corresponding L5 and M5.

I used VALUE function to fix that issue.
Now I need to have each successive scores in the columns.

Originally Posted by ChemistB

Okay so you can have the same name in different rows in the same column and in different columns in the same row. Does it matter what is the 2nd score? Are you reading up and down first or across first?

I'd just like the scores of individuals on sheet4 in their successive columns in the testdb Scores 1 2 3 etc columns.
Example:
a b
1 alice 2
2 bill 3
3 Charlie 2
4 alice 2

As you can see, in this example of sheet4, the name "Alice" appears twice.
However in testdb I'd the above to shown as

a b c
1 name score 1 score 2
2 alice 2 2
3 charlie 2
4 bill 3


Understand? I hope that made my query clearer.

Pl see attached file.
CountIf was not working because numbers are stored as text in testdb Sheet.I have changed it to values in formula.
I have taken the ranges from column D:I, you can extend it also.