If and average

ibf

New Member
Joined
Sep 29, 2006
Messages
18
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
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

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
=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.
 

ibf

New Member
Joined
Sep 29, 2006
Messages
18
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
 

Forum statistics

Threads
1,141,761
Messages
5,708,367
Members
421,566
Latest member
7Nabisco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top