---

---

Excel If Function Multiple Criteria

The IF formula in Excel is a very powerful formula, because you can solve any complex problems with this formula. To understand the formula easily, let me explain some examples. So, let's start the tutorial.

Excel में IF सूत्र बहुत शक्तिशाली सूत्र है, क्योंकि आप इस सूत्र के साथ किसी भी जटिल समस्याओं को हल कर सकते हैं। सूत्र को आसानी से समझने के लिए मैं कुछ उदाहरणों की व्याख्या करता हूं। तो, ट्यूटोरियल शुरू करते हैं।

Excel If function example 1:

Let we have a condition to solve which is shown below in     Fig 1.

Fig 1

In Fig 1. you can see that "If C5 has 80 then D5 will show 50 otherwise it will show 100 in D5. So, here I use IF formula and syntax of IF formula is (logical_test, [value_if_true], [value_if_false]) Here, logical_test means operator with main object. ie our condition is - if C5 has 80 i.e. C5 equals 80. Now, [value_if_true] - if C5 has 80 then 50 will appear and [value_if_false] - If C5 is not equal to 80 then 100 will appear.

Fig 1. में आप देख सकते हैं कि "यदि C5 में 80 है तो D5  में  50 दिखाएगा अन्यथा यह D5 में 100 दिखाएगा। इसलिए, यहां मैं IF सूत्र का उपयोग करता हूं और IF सूत्र का syntax है (logical_test, [value_if_true], [value_if_false])यहां, लॉजिकल_टेस्ट का अर्थ है मुख्य वस्तु के साथ ऑपरेटर । यानी हमारी स्थिति है - यदि C5 में 80 है अर्थात C5 बराबर 80। अब, [value_if_true] - यदि C5 में 80 है तो  50 दिखाई देगा और [value_if_false] - यदि C5 80 के बराबर नहीं है तो 100  दिखाई देगा।

 

logical_test	value_if_true	value_if_false
C5=80	50	100

 

Now, how can we write the formula?

So, firstly click on D5 and write down the formula like this –

= IF(C5=80, 50, 100)

**** Comparisons are =, >, <, <>, <=, >= ****

Excel If function example 2:

Let we have a condition to solve which is shown below in     Fig 2.

Fig 2

In Fig. 2. You can see the question is "If C5 has Akash, it will show 50 in D5 otherwise it will show 100 in D5. Therefore, here I use the IF formula and the syntax of the IF formula is (logical_test, [value_if_true], [value_if_false]) Here, logical_test means the logical operator i.e. our position with the main object - if C5 has Akash that is, C5 is equal to the Akash. Now, [value_if_true] - if C5 has Akash then 50 will show and [value_if_false] - if C5 is not equal to Akash will show 100.

Fig 2. आप देख सकते हैं सवाल यह है कि “यदि C5 में आकाश है तो  D5 में  50 दिखाएगा अन्यथा यह D5 में 100 दिखाएगा। इसलिए, यहां मैं IF सूत्र का उपयोग करता हूं और IF सूत्र का syntax है (logical_test, [value_if_true], [value_if_false]) यहां, लॉजिकल_टेस्ट का अर्थ है कि मुख्य ऑब्जेक्ट के साथ लॉजिकल ऑपरेटर यानि हमारी स्थिति है - यदि C5 में आकाश है अर्थात C5 बराबर आकाश है। अब, [value_if_true] - यदि C5 में आकाश है तो 50 दिखाएगा और [value_if_false] - यदि C5 बराबर नहीं है आकाश तो 100  दिखाएगा।

 

logical_test	value_if_true	value_if_false
C5=”Akash”	50	100

 

Note: - When you entered any text within formula than use block quote (“ “)  

Now, how can we write the formula?

So, firstly click on D5 and write down the formula like this –

= IF(C5=”Akash”, 50, 100)

Excel If function example 3:

Let we have a condition to solve which is shown below in     Fig 3.

Fig 3

In Fig 3. you can see that the question is "If C5 has Akash then Welcome Sir in D5 will show otherwise Sorry! You are not permitted. Here I use IF formula and syntax of IF formula is (logical_test , [value_if_true], [value_if_false]) Here, logical_test means the logical operator i.e. our position with the main object - if C5 has a Akash that is, C5 equals Akash. Now, [value_if_true] - if C5 has Akash then Welcome Sir will show and [value_if_false] - If C5 is not equal to Akash then it will show Sorry! You are not permitted.

Fig 3. में आप देख सकते हैं कि सवाल यह है कि "यदि C5 में आकाश है तो D5  में Welcome Sir दिखाएगा अन्यथा Sorry! You are not permitted दिखाएगा। यहां मैं IF सूत्र का उपयोग करता हूं और IF सूत्र का syntax  है (logical_test, [value_if_true], [value_if_false]) यहां, लॉजिकल_टेस्ट का अर्थ है कि मुख्य ऑब्जेक्ट के साथ लॉजिकल ऑपरेटर यानि हमारी स्थिति है - यदि C5 में आकाश है अर्थात C5 बराबर आकाश है। अब, [value_if_true] - यदि C5 में आकाश है तो Welcome Sir दिखाएगा और [value_if_false] - यदि C5 बराबर नहीं है आकाश तो Sorry! You are not permitted दिखाएगा।

logical_test	value_if_true	value_if_false
C5=”Akash”	Welcome Sir	Sorry! You are not permitted

 

Note: - When you entered any text within formula than use block quote (“ “)  

Now, how can we write the formula?

So, firstly click on D5 and write down the formula like this –

= IF(C5=”Akash”, “Welcome Sir”, “Sorry! You are not permitted”)

                                    OR

= IF(C5 <> ”Akash”, “Sorry! You are not permitted”, “Welcome Sir”)

 

Excel If Function Multiple Criteria:

Let we have a condition to solve which is shown below in Fig 4

Fig 4

In Fig 4. You can see the question is “If a student score greater than equal to 60% than the Division is 1^st Div else score between greater than equal to 45% but less than 60% than Division is 2^nd Div else score between greater than equal to 30% but less than 45% than Division is 3^rd Div  otherwise Division is Fail” So, here I use if formula and the syntax of If formula is IF(logical_test, [value_if_true], [value_if_false])

Now, how can we write the Excel If Function Multiple Criteria?

Here we have three , [value_if_true] condition and one [value_if_false]) so the syntax is like this -

IF(1^st logical_test, [1^st value_if_true], IF(2^nd logical_test, [2^nd value_if_true], IF(3^rd logical_test, [3^rd value_if_true], [value_if_false])))

It means, Excel function checks 1^st logical_test , if it founds true than the result cell value is [1^st value_if_true] else again it checks 2^nd  logical_test , if it founds true than the result cell value is [2^nd value_if_true] else again it checks 3rd logical_test , if it founds true than the result cell value is [3^rd value_if_true] otherwise if it doesn’t match any logical test than it displayed, [value_if_false].

= IF(C8>=60%, “1^st Div”, IF(C8>=45%, “2^nd Div”, IF(C8>=30%, “3^rd Div”, “Fail”)))

 

"Click here to Watch the Video"

Back         Home             Next   

**** End of Tutorial ****

---

---