Using the SUMIF function, you can get the sum of numbers that meet one criterion, such as great than a number, the average. The average is one of the common functions in Excel.
Example: You are working with data that includes the employee's gender, age, and salaries, and you need to get the sum of the salaries that are greater than the average.
The employee's gender is in column A, the age is in column B, and the employees' salaries are in column C.
Formula 1: To get total salaries greater than the average, please use the following formula:
=SUMIF(C2:C12,">"&AVERAGE(C2:C12))
The average is $76,308.82, and the total above the average returns $521,823.00.
Formula 2: To get total salaries less than the average, please use the following formula:
=SUMIF(C2:C12,">"&AVERAGE(C2:C12))
The average is $76,308.82, and the total less than the average is $317,574.00.
Notes: The SUMIF function
The SUMIF function adds all the numbers with the criteria you specified.
Formula:
=SUMIF(range, criteria, [sum_range])
Explanations:
– The range is required, which is the criteria range.
– The criterion is required, the criteria for the sum.
– Sum_range is optional; the cells to add together.