सामान्य सूत्र
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
सारांश
यह गिनने के लिए कि किसी श्रेणी के अंदर एक विशिष्ट शब्द (या कोई विकल्प) कितनी बार दिखाई देता है, आप SUBSTITUTE, LEN और SUMPRODUCT फ़ंक्शन के आधार पर एक सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, C11 में सूत्र है:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
नोट: इस पृष्ठ का सूत्र किसी श्रेणी के किसी शब्द के i nstances को गिनाता है। उदाहरण के लिए, यदि किसी कक्ष में किसी शब्द के दो उदाहरण हैं, तो यह कुल गणना में 2 का योगदान देगा। यदि आप केवल उन कक्षों को गिनना चाहते हैं जिनमें एक विशिष्ट शब्द है, तो COUNTIF फ़ंक्शन के आधार पर यह सरल सूत्र देखें।
स्पष्टीकरण
सूत्र के जेनेरिक संस्करण में, rng जाँच करने के लिए सीमा का प्रतिनिधित्व करता है, और txt शब्द या गिनती का विकल्प है।
दिखाए गए उदाहरण में, B5: B8 जांचने की सीमा है, और C2 में गिनती करने के लिए पाठ (शब्द या विकल्प) है।
श्रेणी के प्रत्येक सेल के लिए, SUBSTITUTE मूल पाठ से सबस्ट्रिंग को हटा देता है और LEN बिना सब्स्क्राइब्ड के पाठ की लंबाई की गणना करता है। यह संख्या तब मूल पाठ की लंबाई से घटा दी जाती है। परिणाम SUBSTITUTE द्वारा हटाए गए वर्णों की संख्या है।
फिर, हटाए गए वर्णों की संख्या को प्रतिस्थापित की लंबाई से विभाजित किया जाता है। इसलिए, यदि एक विकल्प या शब्द 5 वर्ण लंबा है, और मूल पाठ से हटाए जाने के बाद 10 अक्षर गायब हैं, तो हमें पता है कि मूल पाठ में दो बार प्रतिस्थापन / शब्द दो बार दिखाई दिया।
चूँकि उपरोक्त गणना SUMPRODUCT फ़ंक्शन में लिपटी हुई है, इसलिए परिणाम एक ऐसा सरणी है जिसमें श्रेणी के प्रत्येक सेल के लिए एक नंबर होता है। ये संख्याएँ प्रत्येक सेल में सबस्ट्रिंग की घटनाओं की संख्या को दर्शाती हैं। इस उदाहरण के लिए, सरणी इस तरह दिखता है: (1; 1; 0; 1)
अंत में, SUMPRODUCT कोशिकाओं की श्रेणी में प्रतिस्थापित करने की कुल घटनाओं को प्राप्त करने के लिए सरणी में सभी वस्तुओं को एक साथ रखता है।
मामले की अनदेखी
SUBSTITUTE एक केस-संवेदी फ़ंक्शन है, इसलिए यह प्रतिस्थापन को चलाते समय केस से मेल खाएगा। यदि आपको किसी शब्द के ऊपरी या निचले दोनों मामलों को घटाना या प्रतिस्थापित करना आवश्यक है, तो प्रतिस्थापन चलाने से पहले टेक्स्ट को अपरकेस में बदलने के लिए SUBSTITUTE के अंदर UPPER फ़ंक्शन का उपयोग करें:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
