How to sum values or average values based on criteria in another column in excel?

Do you need to report the sum of values or the average of values based on criteria in another column in your report or dashboard? In this post, you will learn how to sum values or average values based on criteria in excel.

If you know how to use the SUMIF/AVERAGEIF function in Microsoft Excel, which helps you to create a summary table to add sum values or average values based on criteria in your excel report. The SUMIF function allows you to add all the cells in the array based on criteria. AVERAGEIF function returns the average of the given array based on criteria.

Suppose you have the below employee sales data and need to create a summary table to display the SUM and AVERAGE of each employee’s sales.

Your final output will look like the below image

How to sum values based on criteria in another column

Please follow the below steps to use the Excel SUMIF function to sum the values based on the criteria

1. List the unique of the employee from column B in column G as in the above image
2. Go to cell H2 and type the below formula to report the sum of sales for each employee

=SUMIF($B$2:$B$21,G2,$D$2:$D$21) You use the Excel SUMIF function to sum the values based on criteria

3. Drag the formula until the cell H6 applies the above formula, and calculate the sum of sales for all employee

How to average values based on criteria in another column

Please follow the below steps to use the Excel AVERAGEIF function to report average values based on the criteria

1. Go to cell I2 and type the below formula to report the average of sales for each employee

=AVERAGEIF($B$2:$B$21,G2,$D$2:$D$21) You use the Excel AVERAGEIF function to average the values based on criteria

2. Drag the formula until the cell I6 applies the above formula, and calculate the average of sales for all employee

Conclusion

You learned Excel SUMIF and AVERAGEIF functions to create a summary report in your excel worksheet. These two function helps to sum or average values based on a single column (employee). You have to use Excel SUMIFS or AVERAGEIFS function If you need to sum or average values based on multiple columns.