SUMIF and AVERAGEIF function

SUMIF and AVERAGEIF function helps you to add or average numbers based on conditions. This function makes use of conditional statement just like the IF function.

In Excel, the SUMIF and AVERAGEIF function calculates the sum and average of numbers in a range that fulfill supplied criteria.

The criteria can include logical operators (>,<,<>,=) and wildcards (*,?).

 

 

SUMIF

SUMIF function calculates the sum of numbers that meet a criterion.

Syntax: =SUMIF(range,criteria,[sum_range])

Arguments

range – one or more cells, including numbers or names, arrays or references.

criteria – a number, expression, cell reference or text.

sum_range – the cells to sum.

 

AVERAGEIF

AVERAGEIF function calculates the average of numbers that meet a criterion.

The syntax: =AVERAGEIF(range,criteria,[average_range])

The arguments

range – one or more cells, including numbers or names, arrays or references.

criteria – a number, expression, cell reference or text.

average_range – the cells to average.

In this worksheet below, you simply want to calculate the sum and average of sales for a criterion (John).

 

SUMIF function in Excel

 

This is what your arguments would look like according to the SUMIF and AVERAGEIF function

range – your range of cells in the worksheet above is A6:A21 (Name column)

criteria – your criteria in this case is “John” in cells A6:A21 (Name column).

Note: In general, when the criteria is a text value, it is enclosed in double quotes (“ “), that’s the reason the criteria John, is written as “John”.

Sum_range/average_range – the cells to sum or average, in this case cell B6:B21 (sales column).

 

SUMIF and AVERAGEIF function

 

In the example shown in the worksheet above, the formulas in cell H7 and I7 is:

=SUMIF(A6:A21,”John”,B6:B21) and =AVERAGEIF(A6:A21,”John”,B6:B21)

So, that’s how the SUMIF and AVERAGEIF function can help you sum and average numbers based on criteria you provide.

Download resource for practice.

SUMIF and AVERAGEIF

You can watch the video below for visual explanation and understand how excel IF function works. Thanks, sharing is caring.