Using sumif with two conditions

[Guest]

Hi,

I wonder if anyone can help. I’m 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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Max]

Welcome, Karl. Thanks for feeding back.