 Tweet

# Excel Tips Tutorial: How to use SUMIF, COUNTIF and AVERAGEIF Functions in Microsoft Excel

In this tutorial Ill teach you some of the useful formulas of Microsoft Excel. The formula of SUM, COUNT and AVERAGE are basic, but if you want to use this formula with any criteria then the formulas of SUMIF, COUNTIF and AVERAGEIF is used.

## COUNTIF

COUNTIF is used to count the number with any criteria.

The formula of COUNTIF is =countif(range,criteria)

Range means the total area in which you want to create the criteria and criteria means what criteria you want to set to count.

I am giving you example that how to use the formula

=countif(B:B,Full time)

B:B represents Column number B and Full time represents that the criteria is to count Full time employees. After pressing enter you will get that how many employees which are full time, you can use any of the criteria you want to count.

## SUMIF

SUMif is used to sum the amount which meets your criteria.

The formula of SUMIF is =sumif(range,criteria,sum_range)

The range and criteria in the formula above is exactly the same as COUNTIF formula, sum_range means the range of numbers or amounts you want to sum.

In the example the formula will be used as =sumif(B:B,Hourly,C:C)

C:C is the column in which the salaries are given. After pressing enter you will get the sum of salaries of employees whose status is hourly.

## AVERAGEIF

AVERAGEIF is used to average the number of amounts with any criteria you give.

The formula of AVERAGEIF is =averageif(range,criteria,average_range)

Once again the range and criteria in the formula above are exactly the same as SUMIF and COUNTIF, average_range represents the range of numbers which you want to average.

In the example the formula will be used as =AVERAGEIF(B:B,"contract",C:C)

C:C is the column which has different salaries which you want to average. After pressing enter you will get the average salaries of contract based employees.

You can also link the criteria from another cell, I wrote Contract in the formula, you can also link it with any other cell. This time the criteria is linked with cell G3 i.e. Contract. The answer will be the same as when I wrote Contract in criteria.

The link of criteria can be set in all three formulas which you have learnt above.    