Excel 2007 : Average if conditional on cell reference, not numerical value

[cde1983]

I am attempting to write an AVERAGEIF formula where the conditional statement (lookup value) is not a numerical value but a reference to a cell in a separate column located on another excel sheet. My data is sorta set up like this...

Sheet 1
A B
126 10
126 26
241 53
241 61
241 73
487 34
487 45
487 67
487 34

Sheet 2
A B
126
487

The formula should average across values in Sheet 1 column B only for values in Sheet 1 column A that are equal to the value present in Sheet 2 column A.

I know the following formula would work:

=AVERAGEIF(Sheet1!A1:A600, "=126",Sheet1!B1:B600)


But, I have a very large excel file with over 6,000 entries and just do not have the time to manually type out each value in Sheet2 column A. I need the formula to automatically calcuate for each value in Sheet 2 column A.

I thought something like the following would work, but it doesn't:

=AVERAGEIF(Sheet1!A1:A600,"=Sheet2!A1",Sheet1!B1:B600)

Any ideas??? Thanks in advance!!!

[ConneXionLost]

How about something like:

=SUMIF(Sheet1!A1:A600,Sheet2!A1,Sheet1!B1:B600)/COUNTIF(Sheet1!A1:A600,Sheet2!A1)

Cheers,

[cde1983]

That works wonderfully! Thank you!