# Excel AVERAGEIF with ‘Greater Than’ and ‘Less Than’ Criteria

### Example 1 – Excel AVERAGEIF with ‘Greater Than’ Criteria

• To find the average for marks greater than 90, enter the following formula in cell H5.
` =AVERAGEIF(D5:D12,">90")`

• You can also apply any of the alternative formulas below to get the same result.
`=AVERAGE(IF(D5:D12>90,D5:D12,""))`
`=SUMIF(D5:D12,">90")/COUNTIF(D5:D12,">90")`
• To find the average marks in physics if the marks in math are greater than 90, enter the following formula instead in cell H5.
`=AVERAGEIF(D5:D12,"<90",E5:E12)`

### Example 2 – Excel AVERAGEIF with ‘Less Than’ Criteria

• Enter the following formula in cell H6 to find the average marks in physics if the marks in math are less than 90.
`=AVERAGEIF(D5:D12,"<90",E5:E12)`

## How to Calculate Average in Excel Using AVERAGIFS Function If a Range Is Between Two Values

• To find the average marks for students with IDs greater than 2 and less than 7 i.e. in groups B and C, enter the following formula in cell H5.
`=AVERAGEIFS(D5:D12,C5:C12,">2",C5:C12,"<7")`

## Things to Remember

• Don’t forget to put double quotes around the criteria if it contains values other than numbers.
• All of the criteria ranges in the AVERAGEIFS function must be the same in size and shape as the average range.
• The formulas may return #DIV/0! if no cells meet the criteria.

## Related Articles

<< Go Back to Excel AVERAGEIF Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF