Using sumif with two conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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.
 
Back
Top