Averaging across worksheets

[Pelham]

I have a bunch of worksheets showing rental income data across four
different cities. Within each city there are about eight (8) different
real estate types (ie 1 bedroom, 2 bedroom etc.).


How can I calculate an average (in a new worksheet) of a specific real
estate type across the four cities when the respective data ranges in
each city do not appear in the same rows in each worksheet? However,
the ranges will be in the same columns across each worksheet.
Furthermore, in a different column on each worksheet will be the name
of the 'real estate type' which is consistent across all cities.
Basically, I am trying to capture the relevant rental income range in
each city where each range corresponds to the same real estate type,
and then take an average of that entire range.


If this does not make sense please let me know - and thanks in
advance...

 

[Paul Sheppard]

I have a bunch of worksheets showing rental income data across four
different cities. Within each city there are about eight (8) different
real estate types (ie 1 bedroom, 2 bedroom etc.).


How can I calculate an average (in a new worksheet) of a specific real
estate type across the four cities when the respective data ranges in
each city do not appear in the same rows in each worksheet? However,
the ranges will be in the same columns across each worksheet.
Furthermore, in a different column on each worksheet will be the name
of the 'real estate type' which is consistent across all cities.
Basically, I am trying to capture the relevant rental income range in
each city where each range corresponds to the same real estate type,
and then take an average of that entire range.


If this does not make sense please let me know - and thanks in
advance...

Hi Pelham

I have assumed that the real estate types are in column A and th
rental rates in column b, you would need to change the reference
accordingly to suit your data

Firstly to count the number of times say a 1 bed is rented use th
following >


=(COUNTIF(Sheet1!$A$1:$A$4,"1 bed")+COUNTIF(Sheet2!$A$1:$A$4,"1 bed"))

You will need to change Sheet1/Sheet2 to match the names of you
worksheets, you will also need to change the ranges A1:A4 to match th
range where your real estate type is, plus you will need to extend th
formula for any additional sheets.

To get count for 2 bed etc just copy the formula to another cell an
change 1 bed to 2 bed

Secondly to get a value for rentals use the following formula >

=(SUMIF(Sheet1!$A$1:$A$4,"
bed",Sheet1!$B$1:$B$4)+SUMIF(Sheet1!$A$1:$A$4,"
bed",Sheet1!$B$1:$B$4))

You will need to change Sheet1/Sheet2 to match the names of you
worksheets, you will also need to change the ranges A1:A4 to match th
range where your real estate type is and your rental values are, plu
you will need to extend the formula for any additional sheets.

To get count for 2 bed etc just copy the formula to another cell an
change 1 bed to 2 bed

Hope this helps

Pau

 

[Pelham]

Thanks - again!

Is there a way to do these COUNTIF and SUM operations by identifying
the range across say 10 worksheet (in the same column on each
worksheet), instead of using these functions 10 times in one formula???

 

[Earl Kiosterud]

Pelham,

Consider putting all the data in one worksheet. It opens up all kinds of
things you can do. Check out "Data across worksheets" at
http://www.smokeylake.com/excel/excel_truths.htm.

 

[Domenic]

If your sheets are named Sheet1 through Sheet10, try...

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:10"))&"'!A1:A100"),
"1 bed"))

....otherwise, try...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B1:B10&"'!A1:A100"),"1 bed"))

....where B1:B10 contains your sheet names.

Hope this helps!