This week we continue to share a set of Excel conditional judgment related formulas. I hope it will be helpful to your work.
COUNTIF
The syntax is as below:
=COUNTIF (condition region, specify condition)
As shown in the figure below, to count all transactions in No.1 sales department, the formula in F2 is: =COUNTIF(B:B,E2)
COUNTIFS
The syntax is as below:
=COUNTIFS (condition region 1, condition region 1, condition region 2, condition region 2…)
As shown in the figure below, It need to count the total transactions in No.1 sales department with a single amount more than 500 (ten thousand Yuan).
The formula in F2 cell is: =COUNTIFS(B:B,E2,C:C,”>500″)
AVERAGEIF and AVERAGEIFS
The function of AVERAGEIF is to calculate the average according to the specified conditions.
The function of AVERAGEIFS is to calculate the average based on multiple conditions.
It’s syntax is like function SUMIF and SUMIFS.
As shown in the figure below, the average sales in No.1 sales department should be calculated.
Formula is: =AVERAGEIF(B:B,E2,C:C)
To calculate the average value in No.1 sales department with a single amount more than 500 (ten thousand yuan).
Formula is: =AVERAGEIFS(C:C,B:B,E2,C:C,”>500”)
MINIFS and MAXIFS
MINIFS and MAXIFS are used to count the minimum and maximum values that meet multiple conditions respectively.
Their syntax is like function SUMIFS.
As shown in the figure below, calculate the maximum sales for the department in column E.
The formula in E2 cell is: =MAXIFS(C:C,A:A,E3)
Well, that’s all we have to share today. Wish you have a good day!