Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with two conditions | Excel Worksheet Functions | |||
sumif more conditions | Excel Worksheet Functions | |||
SUMIF with two conditions? | Excel Worksheet Functions | |||
SUMIF 2 conditions are met??? | Excel Worksheet Functions | |||
sumif with 3 conditions | Excel Worksheet Functions |