combining a lookup formula with summing across sheets

Let's say I give a test in a class every day for ten days and record the scores of everyone who takes it on a different sheet in a workbook. So I wind up with a workbook with ten sheets, each with the same format: names in column A and test scores in column B. But the list isn't sorted the same way on each tab, and the elements may not even be exactly the same (for example, if some students were absent on certain days).

Now I want to add another sheet where I list all the students in column A and add a formula in column B that calculates the average score for each one, regardless of how many times they took the test. So I need to look up the name on each sheet, take the corresponding test score, and then average them.

Does anyone know a way to do this in a single formula?

If not, would it make a difference if I was looking for a simpler calculation, like just the total score for each student instead of the average?

thanks,
peter

Can you post a sample of your workbook?

sure thing -- see attached.

i have found the numbers in the yellow boxes manually. the question is whether there's a way to create a standard formula that i can just copy down either or both column. maybe something with a lookup formula and arrays?

thanks for taking a look. it's been driving me nuts.

peter

Hello Pete_22


First you'll need to list your worksheets in column G3 and down. Then input these formulas below and copy down:

Cell B5:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$3:$G$5&"'!A1:A100"),A5))

Cell C5:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$G$3:$G$5&"'!A1:A100"),A5,INDIRECT("'"&$G$3:$G$5&"'!B1:B100")))/SUMPRODUCT(--(SUMIF(INDIRECT("'"&$G$3:$G$5&"'!A1:A100"),A5,INDIRECT("'"&$G$3:$G$5&"'!B1:B100"))>0))

Example workbook below.

Originally Posted by pete_22

sure thing -- see attached.

i have found the numbers in the yellow boxes manually. the question is whether there's a way to create a standard formula that i can just copy down either or both column. maybe something with a lookup formula and arrays?

thanks for taking a look. it's been driving me nuts.

peter

See the attached

Originally Posted by Teethless mama

See the attached
=AVERAGE(monday!B1,wednesday!B3)

Your formula would work if pete_22 data is set, But if the data fluctuates from time to time I rather use mine because its much more dynamic.

Please Login or Register  to view this content.

thank you both, this is a big help