# If and average

#### ibf

##### New Member
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?

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.

=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.

You could also use a Pivot Table.

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

Replies
4
Views
342
Replies
7
Views
373
Replies
3
Views
301
Replies
12
Views
322
Replies
2
Views
184

1,218,746
Messages
6,144,266
Members
450,533
Latest member
xoxo1998

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back