Average if criteria met

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
Guys,

I've searched everywhere for the solution to this problem so hope someone can help;

I want the formula that will give me the average of Column E only provided Column B has the figure 2 in it.

Column E is numeric values only and I want the average of the values if 2 appears in column B - BUT the columns are on a seperate sheet, (called '1'), and this is what appears to be causing the issues.

Can anyone suggest a solution?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Guys,

I've searched everywhere for the solution to this problem so hope someone can help;

I want the formula that will give me the average of Column E only provided Column B has the figure 2 in it.

Column E is numeric values only and I want the average of the values if 2 appears in column B - BUT the columns are on a seperate sheet, (called '1'), and this is what appears to be causing the issues.

Can anyone suggest a solution?

1) Control+shift+enter, not just enter on all versions:

=AVERAGE(IF('1'!$B$2:$B$100=2,'1'!$E$2:$E$100))

2) Just enter on all versions:

=SUMIF('1'!$B$2:$B$100,2,'1'!$E$2:$E$100)/COUNTIF('1'!$B$2:$B$100,2)

3) Just enter on Excel 2007 or later:

=AVERAGEIF('1'!$B$2:$B$100,2,'1'!$E$2:$E$100)
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
That works great!

Thanks for saving me even more time trying to figure that one out!
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Aladin,

I've got a problem now in that the cell where the result is displayed is showing #DIV/0!

I've looked around for the solution with no success.

So what I need is for the average to be calculated on Sheet 1, Column G, (G7:G10000), but only if '2' appears in Column B, (B7:B10000), ignoring the scenario where there are no cells that match the criteria.

Can you help?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Aladin,

I've got a problem now in that the cell where the result is displayed is showing #DIV/0!

I've looked around for the solution with no success.

So what I need is for the average to be calculated on Sheet 1, Column G, (G7:G10000), but only if '2' appears in Column B, (B7:B10000), ignoring the scenario where there are no cells that match the criteria.

Can you help?

Not knowing which option you picked out...

1) Control+shift+enter, not just enter on all versions:
Rich (BB code):
=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
  AVERAGE(IF('1'!B7:B10000=2,'1'!G7:G10000))))

2) Just enter on all versions:
Rich (BB code):
=SUMIF('1'!B7:B10000,2,'1'!G7:G10000)/
   MAX(1,COUNTIF('1'!B7:B10000,2))

3) Just enter on Excel 2007 or later:
Rich (BB code):
=IFERROR(AVERAGEIF('1'!B7:B10000,2,'1'!G7:G10000),0)

All are modified to return 0 when #DIV/0! would show up.
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Option 2 worked a treat - thank you!

I didn't try Option 1 but did Option 3 and that gave me a #Name? error so not sure what went wrong there.

Doesn't matter anyway - your 2nd Option worked so thanks again!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Option 2 worked a treat - thank you!

I didn't try Option 1 but did Option 3 and that gave me a #Name? error so not sure what went wrong there.

Doesn't matter anyway - your 2nd Option worked so thanks again!

You are welcome. Thanks for providing feedback. By the way, option 3 requires Excel 2007 or later.
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
Of course it does, as pointed out in your original reply - my fault for not reading properly!

I have lots of things to do with this workbook, (especially with Mail Merge so if you know lots about that then great!), no doubt I'll be in touch for more advice!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Of course it does, as pointed out in your original reply - my fault for not reading properly!

I have lots of things to do with this workbook, (especially with Mail Merge so if you know lots about that then great!), no doubt I'll be in touch for more advice!

No problem. I think there are some folks around here with knowledge about mail merge.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

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