If and average

[ibf]

I need help. I've create a spreadsheet that includes many towns with subtotals. I figured out how to create a SUMIF statement for a specific town, now I want the average for the for the specific towns.

My IF formula is =SUMIF(E2:E100,"Port Hope",I2:I100)

How do I get the the average for the town?

 

[Seti]

Welcome to MR Excel.

You just need a count to divide the sum by.

=COUNTIF(E2:E100,"Port Hope")

Just watch out for division by 0 if you have no cells for a specific town.

 

[GorD]

=AVERAGE(IF(E2:E100="port hope",I2:I100))

should work which is an array formula therefore enter with ctrl shift enter, rather than just enter.

 

[Brian from Maui]

You could also use a Pivot Table.

 

[ibf]

Thanks for the quick reply Seti; GorD and Brian from Maui

The formula that I was looking for was from GorD.
I kind of though that I had to combine the Average with an IF statement, but wasn't sure how to go about doing it.

I'm new to working with Excel spreadsheets and I appreciated the quick response to my question.

Thanks again for your time and help.
ibf