सामान्य सूत्र
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
सारांश
यह सुनिश्चित करने के लिए कि कोशिकाओं में एक या एक पाठ स्ट्रिंग है (या जिसमें "कैट" या "चूहा" शामिल है) आप ISNUMBER + SEARCH या FIND के साथ SUMPRODUCT फ़ंक्शन का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, सेल F5 में सूत्र है:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
जो C4: C8 में मानों को वापस लौटाता है जब B4: B8 में कोशिकाओं में "कैट" या "रैट" होते हैं।
स्पष्टीकरण
जब आप "या" मानदंड के साथ कोशिकाओं को योग करते हैं, तो आपको सावधान रहना होगा कि गिनती दोगुनी न हो जब संभावना है कि दोनों मानदंड वापस आ जाएंगे। दिखाए गए उदाहरण में, हम स्तंभ C में मानों को जोड़ना चाहते हैं जब कॉलम B में कोशिकाओं में "बिल्ली" या "चूहा" होता है। हम SUMIF का उपयोग दो मानदंडों के साथ नहीं कर सकते हैं, क्योंकि SUMIFS AND तर्क पर आधारित है। और अगर हम दो SUMIFS (यानी SUMIFS + SUMIFS) का उपयोग करने की कोशिश करते हैं, तो हम दो बार गिनती करेंगे क्योंकि ऐसी कोशिकाएं हैं जिनमें "बिल्ली" और "चूहा" दोनों शामिल हैं।
इसके बजाय, हम इस तरह एक सूत्र का उपयोग करते हैं:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
इस सूत्र का यह मूल सूत्र यहाँ समझाया गया सूत्र है जो ISNUMBER और SEARCH के साथ एक सेल के अंदर पाठ का पता लगाता है:
ISNUMBER(SEARCH("text",range)
जब कोशिकाओं की एक श्रृंखला दी जाती है, तो यह स्निपेट TRUE / FALSE मूल्यों की एक सरणी लौटाएगा, प्रत्येक सेल के लिए एक मान सीमा। इस सूत्र में, हम इस स्निपेट का दो बार उपयोग करते हैं, एक बार "बिल्ली" के लिए और एक बार "चूहे" के लिए, इसलिए हमें दो सरणियाँ मिलेंगी। इस बिंदु पर, हमारे पास:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
इसके बाद, हम इन सरणियों को एक साथ जोड़ते हैं क्योंकि इसके अलावा OR लॉजिक के लिए बूलियन बीजगणित में उपयोग किया जाता है। गणित ऑपरेशन स्वचालित रूप से 1s और 0s के लिए TRUE और FALSE मूल्यों को बढ़ाता है, इसलिए हम नीचे दिए गए सरणी के साथ समाप्त होते हैं:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
इस सरणी में प्रत्येक संख्या मूल दो सरणियों में TRUE और FALSE मूल्यों को एक साथ जोड़ने का परिणाम है। दिखाए गए उदाहरण में, सरणी इस तरह दिखता है:
(2;0;2;1;0)
हमें इन नंबरों को जोड़ने की आवश्यकता है, लेकिन हम डबल गिनती नहीं करना चाहते हैं। इसलिए हमें यह सुनिश्चित करने की आवश्यकता है कि शून्य से अधिक किसी भी मूल्य को केवल एक बार गिना जाए। ऐसा करने के लिए, हम सभी मानों को TRUE या FALSE के लिए "> 0" के साथ सरणी की जाँच करके बाध्य करते हैं। यह TRUE / FALSE देता है:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
जिसे हम एक दोहरे नकारात्मक (-) का उपयोग करके 1/0 में परिवर्तित करते हैं:
=SUMPRODUCT((1;0;1;1;0),C4:C8)
और अंत में:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT दो सरणियों के संबंधित तत्वों को एक साथ गुणा करता है और परिणाम को वापस करता है, 70 लौटाता है।
केस-संवेदी विकल्प
खोज समारोह मामले की अनदेखी करता है। यदि आपको संवेदनशील विकल्प की आवश्यकता है, तो खोज समारोह के साथ खोज को बदलें।
