How to average same cell across worksheets given a condition

[sgoldman]

Hello -

I'm trying to create a master Workbook that can take the average of the values of an individual cell across multiple workbooks, but given a certain condition is met. For example, I want to average all of the values of C3 (which is a number) across all worksheets, but only when cell A4 says "Sales Department" in that same worksheet.


Is there a formula that can help me with this? Thank you! This is my first post here, so please let me know if there's anything else I need.

 

[Eric W]

Welcome to MrExcel!

It can be done, but it's tricky:

Book3
	A	B	C	D	E	F	G	H	I
1						Sheets		Department	C3 Average
2			Number			Sheet4		Sales Department	7.5
3			10			Sheet5
4	Sales Department					Sheet6
Sheet4

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT(SUMIF(INDIRECT("'"&F2:F4&"'!A4"),H2,INDIRECT("'"&F2:F4&"'!c3")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&F2:F4&"'!A4"),H2))

You need a list of your sheets, and a complicated formula. If you're open to a UDF (User-Defined Function), which requires enabling macros, it would be much easier.

 

[Norie]

same cell across worksheets

individual cell across multiple workbooks

Is it worksheets or workbooks?

 

[sgoldman]

Is it worksheets or workbooks?

sheets, good catch! thanks!