AVERAGEIFS, SUMIFS and COUNTIFS Functions in Microsoft Excel

In this article, we will learn how to apply “Averageifs”, “Sumifs” and “Countifs” in Microsoft Excel 2013.

AVERAGEIFS: Finds average (arithmetic mean) for the cells specified by a given set of conditions or criteria.

Syntax: =AVERAGEIFS(average_range,criteria_range,criteria,...)

average_range: The range of cells for that you want to derive an average.

criteria_range: The range of cells from which you want criteria to evaluate.

Criteria: The criteria define which cells to average.

Let’s take an example to understand how and where we can use the Averageifs Excel function.

We have data in range A1:C10. Column A contains Date, Column B contains Agent and Column C contains Amount. We want to return the average to the amount for an Agent and date wise.
img1

 

Follow below given steps to return the average for two criteria:-

  • Select the cell C13, and write the formula.
  • =AVERAGEIFS(C1:C10,A1:A10,A13,B1:B10,B13)
  • Press Enter on your keyboard.
  • You will get the average amount according to an Agent and date wise.

img2

 

SUMIFS function is used for adding the cells specified by a given set of condition or criteria.

The syntax of SUMIFS formula:-  =SUMIFS(sum_range,criteria_range1,criteria1,…)

How to use SUMIFS formula in Excel?

We want to return the total to the amount for an Agent and date wise.

 
img3

 

Follow below given steps to return the total according to two criteria:-

  • Select the cell C13, and write the formula.
  • =SUMIFS(C1:C10,A1:A10,A13,B1:B10,B13)
  • Press Enter on your keyboard.
  • You will get the total amount according to an Agent and date wise.

img4

 

COUNTIFS function is used to count the entries in multiple range based on multiple criteria.

Syntax of “COUNTIFS” function: 

=COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)

How to use COUNTIFS function in Excel?

We want to return the total of the amount for an Agent and date wise.

 
img5

 

Follow below given steps to return the total according to two criteria:-

  • Select the cell C13, and write the formula.
  • =COUNTIFS(A1:A10,A13,B1:B10,B13)
  • Press Enter on your keyboard.
  • You will get the no. of agents according to an Agent and date wise.

img6

 

download

Download - AVERAGEIFS, SUMIFS and COUNTIFS Functions New functions- xlsx

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.