Using sumif with two conditions
 

Hi,

I wonder if anyone can help. Im creating a spreadsheet to track house
prices in several areas. I want to track average prices.

At the top of the spreadsheet I want to create a table with a line for each
month of year. The table would have four columns, one for 1 bedroom houses,
one for 2 bedroom houses and so on up four beds.

So, in the cell for the 1 beds in Jan I need a formula that runs something
like this:

Look at all the cells in the range A4-A25. If the date in the cell is in
January, check corresponding cell in column B to see if it reads €œ1 bed€�.
Calculate the average of all the cells in the range that have the values
€œdate in January€� and €œ1 bed€�.

Can anyone tell me what function will do that. Can I do if with a sumif
statement?

Many thanks in advance for any help or advice: all much appreciated.

Karl

Using sumif with two conditions
 

One way

Assuming col A = real dates, col B = descriptions eg "1 bed",
col C = prices to be averaged

Place in say, D4, array-enter with CTRL+SHIFT+ENTER:
=AVERAGE(IF((TEXT(A4:A25,"mmmyy")="Jan07")*(B4:B25 ="1 bed"),C4:C25))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Karl" wrote:
Hi,

I wonder if anyone can help. Im creating a spreadsheet to track house
prices in several areas. I want to track average prices.

At the top of the spreadsheet I want to create a table with a line for each
month of year. The table would have four columns, one for 1 bedroom houses,
one for 2 bedroom houses and so on up four beds.

So, in the cell for the 1 beds in Jan I need a formula that runs something
like this:

Look at all the cells in the range A4-A25. If the date in the cell is in
January, check corresponding cell in column B to see if it reads €œ1 bed€�.
Calculate the average of all the cells in the range that have the values
€œdate in January€� and €œ1 bed€�.

Can anyone tell me what function will do that. Can I do if with a sumif
statement?

Many thanks in advance for any help or advice: all much appreciated.

Karl

Using sumif with two conditions
 

Another way would be to use the SUMPRODUCT() function. Rather than try to
explain in detail here, and because I was a little confused by the addresses
and ranges you used (I confuse easily at times), I built a sample workbook
with the formula built up in it, some sample data, and explanation of how the
formula works. You can download the workbook by clicking this link and
choosing to save the file on your computer. It's in format compatible with
Excel 97-2003 (and 2007)

http://www.jlathamsite.com/uploads/AverageHomeCosts.xls


"Karl" wrote:

Hi,

I wonder if anyone can help. Im creating a spreadsheet to track house
prices in several areas. I want to track average prices.

At the top of the spreadsheet I want to create a table with a line for each
month of year. The table would have four columns, one for 1 bedroom houses,
one for 2 bedroom houses and so on up four beds.

So, in the cell for the 1 beds in Jan I need a formula that runs something
like this:

Look at all the cells in the range A4-A25. If the date in the cell is in
January, check corresponding cell in column B to see if it reads €œ1 bed€�.
Calculate the average of all the cells in the range that have the values
€œdate in January€� and €œ1 bed€�.

Can anyone tell me what function will do that. Can I do if with a sumif
statement?

Many thanks in advance for any help or advice: all much appreciated.

Karl

Using sumif with two conditions
 

Thank you both very much for your help. And to J Latham, thank you very much
indeed for the sample file. That really was very kind.

"Karl" wrote:

Hi,

I wonder if anyone can help. Im creating a spreadsheet to track house
prices in several areas. I want to track average prices.

At the top of the spreadsheet I want to create a table with a line for each
month of year. The table would have four columns, one for 1 bedroom houses,
one for 2 bedroom houses and so on up four beds.

So, in the cell for the 1 beds in Jan I need a formula that runs something
like this:

Look at all the cells in the range A4-A25. If the date in the cell is in
January, check corresponding cell in column B to see if it reads €œ1 bed€�.
Calculate the average of all the cells in the range that have the values
€œdate in January€� and €œ1 bed€�.

Can anyone tell me what function will do that. Can I do if with a sumif
statement?

Many thanks in advance for any help or advice: all much appreciated.

Karl

Using sumif with two conditions
 

Welcome, Karl. Thanks for feeding back.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Karl" wrote in message
...
Thank you both very much for your help. And to J Latham, thank you very
much
indeed for the sample file. That really was very kind.