Today, let’s share a set of Excel conditional judgment related formulas, hope to improve work efficiency.
IF
The syntax is as below:
=IF (Determine the condition, return this value IF the condition is True, return this value IF the condition is False)
As shown in the figure below, the salesman’s level should be judged according to the sales in column C. Those greater than or equal to 5 million yuan are considered “good”, otherwise they are considered “bad”.
The formula in D2 is: =IF (C2>=500,”good”,”bad”)
IFS
IFS can complete the judgment of multiple conditions.
The syntax is as below:
=IFS (condition 1, result 1, condition 2, result 2……TRUE, return this value if all conditions are False)
As shown in the figure below, the corresponding grade should be given according to the sales in column D.
The rule is: Above 50,000 is level A, above 30,000 is level B, and all other are level C.
The formula in E2 is: =IFS (D2>50000,”A”,D2>30000,”B”,TRUE,”C”)
SUMIF
The syntax is as below:
=SumIf (conditional region, summation condition, summation region)
As shown in the figure below, the total sales of this department should be calculated according to the departments in column E.
The formula in F2 is: =SUMIF(B:B,E2,C:C)
If we use the following syntax, we are summing over the conditional region:
= sumIf (conditional region, specified summation condition)
As shown in the figure below, the total sales of a single amount of more than 500 (ten thousand Yuan) shall be counted.
Formula in E2 is: =SUMIF (C:C,”>500”)
SUMIFS
The syntax is as below:
=SUMIFS (summation region, condition region 1, condition 1, condition region 2, condition 2…)
Pay attention, the summation region should be written at the very beginning.
As shown in the figure below, it need to count the total sales in No.1 sales department with a single amount of more than 500 (ten thousand yuan).
The formula in F2 is: =SUMIFS(C:C,B:B,E2,C:C,”>500”)
Well, that’s all for today. See you next time.