Excel में SUMIFS() फ़ंक्शन: एक से अधिक मानदंडों के साथ डेटा का योग
Excel में SUMIFS() फ़ंक्शन उन मानों का योग करता है जो कई मानदंडों को पूरा करते हैं, जिससे यह संख्याओं, तिथियों और पाठ के साथ सशर्त गणनाओं के लिए उपयोगी बनता है। सिंटैक्स, उदाहरण, और सर्वोत्तम अभ्यासों के साथ SUMIFS() का उपयोग करना सीखें।
Excel में SUMIFS() फ़ंक्शन कई शर्तों के आधार पर संख्याएँ जोड़ता है, जिससे हमें गणनाओं पर सटीक नियंत्रण मिलता है। SUMIF() के विपरीत, जो एक शर्त की जाँच करता है, SUMIFS() एक साथ कई शर्तों को संभालता है।
इस लेख में, मैं आपको उन्नत उदाहरणों और समस्या निवारण सुझावों के साथ SUMIFS() का उपयोग करना दिखाऊँगा। अंत तक, आप जान जाएँगे कि अपनी गणनाओं को अधिक सटीक और कुशल बनाने के लिए SUMIFS() का कैसे उपयोग करें।
त्वरित उत्तर
Excel में SUMIFS() फ़ंक्शन का उपयोग करने के लिए:
फ़ंक्शन शुरू करने के लिए =SUMIFS( टाइप करें।
वह रेंज चुनें जिसमें जोड़ने के लिए मान हैं।
वह रेंज चुनें जहाँ शर्त लागू होगी।
मिलान करने के लिए शर्त दर्ज करें।
कोष्ठक बंद करें और Enter दबाएँ।
यहाँ, मैंने उन सभी बिक्री का योग किया है जो 200 से अधिक हैं।
SUMIFS() का उपयोग करके मानों का योग करें। चित्र: लेखक।
SUMIFS() फ़ंक्शन को समझना
Excel में SUMIFS() फ़ंक्शन केवल तब मानों का योग करता है जब सभी निर्दिष्ट शर्तें पूरी होती हैं। यह बड़ा >, छोटा <, बराबर =, और असमान <> जैसे तर्कसंगत ऑपरेटरों के साथ-साथ आंशिक मिलान के लिए वाइल्डकार्ड का समर्थन करता है। यह फ़ंक्शन संख्याओं, पाठ और तिथियों के साथ काम करता है।
SUMIFS() का वाक्यविन्यास
SUMIFS() फ़ंक्शन पहले जोड़ने के लिए मानों की रेंज तय करता है, उसके बाद शर्त वाली रेंज और मानदंड की जोड़ियाँ आती हैं। ध्यान दें कि SUMIFS() एक से अधिक तर्कसंगत शर्तों को संभाल सकता है।
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)संचालित द्वारा
यहाँ:
sum_range वे कोशिकाएँ हैं जिनका आप योग करना चाहते हैं।
criteria_range1 पहली रेंज है जहाँ हम शर्त जाँचते हैं।
criteria1 वह शर्त है जो criteria_range1 पर लागू होती है।
[criteria_range2, criteria2], … वैकल्पिक हैं। आप और रेंज-शर्त की जोड़ियाँ जोड़ सकते हैं। हर जोड़ी एक अतिरिक्त शर्त जोड़ती है जिसे पूरा होना चाहिए।
ध्यान दें कि criteria_range(वह रेंज जहाँ शर्तें जाँची जाती हैं) में पंक्तियों और स्तंभों की संख्या sum_range (जिस रेंज का योग हो रहा है) के समान होनी चाहिए।
SUMIFS() उदाहरण
आइए एक बुनियादी उदाहरण आज़माते हैं। मेरे पास फलों का एक डेटासेट है जिसमें उनकी मात्रा और बिक्री है। और मैं उन Apples की कुल बिक्री जानना चाहता/चाहती हूँ जहाँ Quantity 5 से अधिक है। तो मेरा फ़ॉर्मूला होगा:
=SUMIFS(C2:C8, A2:A8, "Apples", B2:B8, ">5")संचालित द्वारा
यहाँ C2:C8 जोड़ने के लिए मानों की रेंज है (Sales ($))। A2:A8, "Apples" वह शर्त है जिससे केवल वे पंक्तियाँ शामिल हों जहाँ उत्पाद Apples है। और B2:B8, ">5" वह शर्त है जिससे केवल वे पंक्तियाँ शामिल हों जहाँ Quantity 5 से अधिक है।
शर्त पर बिक्री का योग करने के लिए SUMIFS() फ़ॉर्मूला। चित्र: लेखक।
SUMIF() बनाम SUMIFS()
SUMIF() और SUMIFS() के बीच मुख्य अंतर शर्तों की संख्या है। SUMIF() एकल शर्त संभालता है। जबकि SUMIFS() कई शर्तों को संभाल सकता है।
विशेषता
SUMIF()
SUMIFS()
शर्तों की संख्या
केवल एक शर्त
एकाधिक शर्तें (127 तक)
लॉजिक प्रकार
सरल फ़िल्टरिंग
AND लॉजिक (सारी शर्तें पूरी होनी चाहिए)
वाक्यविन्यास
=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, …)
आर्ग्युमेंट क्रम
पहले criteria range, अंत में sum range
पहले sum range, फिर criteria की जोड़ियाँ
किसके लिए उपयुक्त
सरल फ़िल्टरिंग कार्य
कई शर्तों के साथ जटिल डेटा विश्लेषण
इसे दूसरे तरीके से ऐसे समझें कि SUMIFS(), SUMIF() का विस्तारित रूप है। मैं ऐसा इसलिए कहता/कहती हूँ क्योंकि SUMIFS() एक ही शर्त को भी संभाल सकता है, इसलिए यदि आप SUMIFS() को केवल एक शर्त के साथ उपयोग करते हैं, तो यह SUMIF() की तरह ही काम करता है। इसका मतलब है कि SUMIF() की जगह SUMIFS() चुनना तकनीकी रूप से कभी गलत नहीं है। आप हमारे Excel फ़ॉर्मूला चीट शीट देख सकते हैं ताकि और उपयोगी Excel फ़ॉर्मूलों का पता लगा सकें।
Excel में SUMIFS() के उपयोग के मामले
अब जबकि आप जानते हैं कि SUMIFS() कैसे काम करता है, आइए कुछ उन्नत मामले देखें जहाँ हम इसे वास्तविक दुनिया में उपयोग कर सकते हैं।
विभिन्न डेटा प्रकारों के साथ SUMIFS() का उपयोग
SUMIFS() संख्याएँ, पाठ, और तिथियों जैसे विभिन्न डेटा प्रकारों के साथ काम कर सकता है। मान लें मेरे पास बिक्री रिपोर्ट है, लेकिन कुछ पंक्तियों में ग्राहक के नाम गायब हैं। और मैं केवल उन्हीं का योग करना चाहता/चाहती हूँ जहाँ ग्राहक का नाम मौजूद है। इसके लिए, मैं निम्न फ़ॉर्मूला उपयोग करता/करती हूँ:
SUMIFS() फ़ंक्शन से खाली कोशिकाओं का योग करें। चित्र: लेखक।
शर्त को हार्डकोड करने की बजाय, हम उसे किसी अन्य सेल में रख सकते हैं। यदि मैं Sarah Lee के लिए बिक्री का योग करना चाहता/चाहती हूँ, तो मैं उसे E3 सेल में टाइप करता/करती हूँ और यह फ़ॉर्मूला उपयोग करता/करती हूँ:
जैसे ही E3 में मान बदलेगा, कुल अपने-आप अपडेट हो जाएगा।
सेल रेफरेंस के साथ मानों का योग करने के लिए SUMIFS() का उपयोग करें। चित्र: लेखक।
SUMIFS() में OR लॉजिक लागू करना
हम जानते हैं कि SUMIFS() केवल AND लॉजिक का उपयोग करता है, यानी जो शर्तें हम तय करते हैं वे सभी सही होनी चाहिए तभी मान जोड़े जाएँगे। हालाँकि, यदि हम OR लॉजिक चाहते हैं, जो कई शर्तों में से कोई एक पूरी होने पर भी मान जोड़ देता है, तो हम कई SUMIFS() को जोड़कर ऐसा कर सकते हैं।
उदाहरण के लिए, यदि मैं John Doe या Drake White में से किसी एक की बिक्री का योग करना चाहता/चाहती हूँ, तो मैं दो SUMIFS() फ़ॉर्मूलों को ऐसे जोड़ता/जोड़ती हूँ:
=SUMIFS(C2:C8, A2:A8, "John Doe") + SUMIFS(C2:C8, A2:A8, "Drake White")संचालित द्वारा
क्योंकि Drake White सूची में नहीं है, फ़ॉर्मूले का वह भाग 0 लौटाता है। कुल में केवल John Doe की बिक्री शामिल होती है, जो दर्शाता है कि OR लॉजिक कैसे काम करता है।
SUMIFS() फ़ंक्शन में OR लॉजिक का उपयोग करें। चित्र: लेखक।
Excel 365 या बाद के संस्करणों में, हम इसे एक array फ़ॉर्मूला से सरल बना सकते हैं और यह वही परिणाम देगा।
=SUMIFS(C2:C8,A2:A8,{"John Doe","Drake White"})संचालित द्वारा
SUMIFS() array फ़ॉर्मूला का उपयोग करें। चित्र: लेखक।
Named ranges के साथ SUMIFS()
हम सेल रेफरेंस जैसे A2:A5 या C2:C5 की बजाय named ranges का भी उपयोग कर सकते हैं। इस उदाहरण में, मैं A2:A5 चुनकर उसे Customer_list नाम देता/देती हूँ और C2:C5 चुनकर उसे Sales_list नाम देता/देती हूँ. अबJohn की कुल बिक्री खोजने के लिए John, मैं यह फ़ॉर्मूला उपयोग करता/करती हूँ:
=SUMIFS(Sales_list, Customer_list, "John")संचालित द्वारा
Named ranges के साथ SUMIFS() से मानों का योग करें। चित्र: लेखक।
आंशिक मिलान के लिए वाइल्डकार्ड का उपयोग
वाइल्डकार्ड विशेष अक्षर (* या ?) होते हैं जो पाठ खोज और पैटर्न मिलान में अज्ञात या परिवर्तनीय अक्षरों का प्रतिनिधित्व करते हैं:
* किसी भी संख्या के अक्षरों से मेल खाता है (शून्य सहित)।
? ठीक एक अक्षर से मेल खाता है।
यह ऐसे काम करते हैं:
A* उन सभी कोशिकाओं से मेल खाएगा जो A से शुरू होती हैं।
*A उन सभी कोशिकाओं से मेल खाएगा जो A पर समाप्त होती हैं।
*A* उन सभी कोशिकाओं से मेल खाएगा जिनमें कहीं भी A शामिल है।
SUMIFS() फ़ंक्शन वाइल्डकार्ड अक्षरों के माध्यम से पाठ-मिलान की क्षमता प्रदान करता है। यह उन मानों की पहचान करता है जो किसी विशिष्ट पाठ पैटर्न से शुरू, समाप्त, या उसे समाहित करते हैं। मान लें मैं उस Product Code के लिए बिक्री का योग करना चाहता/चाहती हूँ जो ABC से शुरू होता है। इस स्थिति में, मेरा फ़ॉर्मूला ऐसा होगा:
SUMIFS() में वाइल्डकार्ड का उपयोग करें। चित्र: लेखक।
तिथि सीमाओं के आधार पर योग करना
SUMIFS() किसी विशिष्ट तिथि सीमा के भीतर के मानों का भी योग कर सकता है। यदि मैं जनवरी 2023 की सारी बिक्री जानना चाहता/चाहती हूँ, तो मैं यह फ़ॉर्मूला उपयोग करता/करती हूँ:
=SUMIFS(D2:D8, B2:B8, ">=01-Jan-2023", B2:B8, "<=31-Jan-2023") संचालित द्वारा
यह फ़ॉर्मूला 1 जनवरी 2023 से 31 जनवरी 2023 तक की तिथियों की जाँच करेगा और फिर सारी बिक्री का योग करेगा।
SUMIFS() का उपयोग करके तिथि सीमा का योग करें। चित्र: लेखक।
एकाधिक संख्यात्मक शर्तों के साथ मानों का योग
SUMIFS() फ़ंक्शन उन मानों का योग कर सकता है जो विशिष्ट संख्यात्मक सीमाओं में आते हैं। यहाँ, मेरे पास एक रैंडम डेटासेट है और मैं उन मानों का योग करना चाहता/चाहती हूँ जहाँ Sales 100 से अधिक लेकिन 300 से कम है। इसके लिए, मैं निम्न फ़ॉर्मूला उपयोग करता/करती हूँ:
=SUMIFS(C2:C5, C2:C5, ">100", C2:C5, "<300")संचालित द्वारा
यह फ़ॉर्मूला पहले पहले argument के रूप में जोड़ने के लिए कोशिकाओं की रेंज (C2:C5) बताता है। फिर, यह उसी रेंज पर उपर्युक्त दो शर्तें लागू करता है। SUMIFS() फ़ंक्शन रेंज की प्रत्येक कोशिका का दोनों मानदंडों के विरुद्ध एक साथ मूल्यांकन करता है। और आप देख सकते हैं कि केवल वे कोशिकाएँ जिनके मान 100 और 300 के बीच हैं (exclusive) योग में शामिल होती हैं।
SUMIFS() का उपयोग करके संख्या सीमा के भीतर मानों का योग करें। चित्र: लेखक।
यदि SUMIFS() काम न करे तो समस्या निवारण
यदि आपका SUMIFS() फ़ंक्शन अपेक्षा के अनुसार काम नहीं कर रहा है, तो निम्न समस्याओं की जाँच करें:
रेंज आकार का मेल न होना
यदि आपका SUMIFS() फ़ॉर्मूला त्रुटि देता है, तो इसका कारण रेंज आकार का मेल न होना हो सकता है। SUMIFS() में, आपके द्वारा चुनी गई सभी रेंज, जैसे sum_range और criteria_ranges, में पंक्तियों और स्तंभों की संख्या समान होनी चाहिए।
उदाहरण के लिए, इस फ़ॉर्मूले में sum range B2:B8 है, लेकिन criteria range A2:A7 है, जिसमें एक पंक्ति कम है:
SUMIFS() में, पाठ मानदंड को उद्धरण चिह्नों में रखना अनिवार्य है। यदि आप ऐसा नहीं करते, तो यह गलत परिणाम दे सकता है। उदाहरण के लिए, यह फ़ॉर्मूला 0 लौटाएगाक्योंकि Excel Drake White को पाठ नहीं बल्कि नाम या चर के रूप में देखता है:
यदि फ़ॉर्मूला आपके अपेक्षित परिणाम की जगह 0 लौटाता है, तो यह संख्यात्मक या तिथि-आधारित शर्तों के गलत फ़ॉर्मैट के कारण हो सकता है। SUMIFS() में, >100 या <01-Jan-2023 जैसी शर्तें उद्धरण चिह्नों में पाठ स्ट्रिंग के रूप में लिखी जानी चाहिए। अन्यथा, यह त्रुटि या 0 लौटा सकता है क्योंकि शर्त उद्धरण में नहीं है।
या हम तिथि को किसी अलग सेल (जैसे C1) में रख सकते हैं और यह उपयोग कर सकते हैं:
SUMIFS() कुछ रूपांतरों का समर्थन नहीं करता, जैसे फ़ॉर्मूला के भीतर डेटा से विशिष्ट घटक निकालना। उदाहरण के लिए, यदि मैं जनवरी और मार्च की बिक्री का योग करना चाहता/चाहती हूँ, तो SUMIFS() सीधे {"January","March"} जैसी array criteria के साथ काम नहीं करेगा।
इसे दिखाने के लिए, मान लें मेरे पास कॉलम A में महीने (January, February, March, April) और कॉलम B में बिक्री मान (100, 200, 300, 400) हैं। फ़ॉर्मूला
=SUMIFS(B2:B5, A2:A5, {"January","March"})संचालित द्वारा
अपेक्षित योग 400 (100 + 300) नहीं लौटाएगा। इसके बजाय, हम या तो SUMPRODUCT() का उपयोग कर सकते हैं:
=SUMPRODUCT((A2:A5={"January","March"})*B2:B5)संचालित द्वारा
या इस फ़ॉर्मूले के साथ SUMIFS() को SUM() में रैप कर सकते हैं:
=SUM(SUMIFS(B2:B5, A2:A5, {"January","March"}))संचालित द्वारा
दोनों समाधान array criteria को सही ढंग से संभालेंगे और जनवरी तथा मार्च की बिक्री का योग लौटाएँगे।
SUMIFS() का उपयोग करते समय सर्वोत्तम अभ्यास
SUMIFS() फ़ंक्शन का अधिकतम लाभ उठाने के लिए, इन सर्वोत्तम अभ्यासों का पालन कर आम गलतियों से बचें:
लचीले फ़िल्टरिंग के लिए वाइल्डकार्ड का उपयोग करें: डेटा अक्सर पूरी तरह व्यवस्थित नहीं होता। हो सकता है उत्पाद नाम हमेशा एक जैसे न लिखे हों, या अतिरिक्त स्पेस हों। वाइल्डकार्ड आपको सटीक पाठ की जरूरत के बिना पैटर्न मिलाने में मदद करते हैं।
मानदंड हार्डकोड करने से बचें: शर्तों को सीधे फ़ॉर्मूला में टाइप करने के बजाय, उन्हें अलग सेल में रखें। यदि आप "Drake White" या ">100" जैसी शर्त हार्डकोड करते हैं, तो हर बार बदलाव के लिए फ़ॉर्मूला संपादित करना होगा। जबकि सेल रेफरेंस के साथ, फ़ॉर्मूला अपने-आप अपडेट हो जाएगा।
फ़ॉर्मूले कॉपी करते समय absolute references का उपयोग करें: जब आप SUMIFS() फ़ॉर्मूला अन्य कोशिकाओं में कॉपी करते हैं, तो Excel स्वतः रेफरेंस बदल देता है। यदि आप ऐसा नहीं चाहते, तो कोशिकाओं को लॉक करने के लिए $ जोड़कर absolute references का उपयोग करें।
अंतिम विचार
SUMIFS() एक Excel फ़ंक्शन है जो विशिष्ट मानदंडों से मेल खाते संख्याओं को फ़िल्टर करके जोड़ने में हमारी मदद करता है। यह क्षेत्रवार बिक्री ट्रैक करने, श्रेणीवार खर्चों का सारांश बनाने, या उत्पाद प्रकार के अनुसार इन्वेंट्री गिनने जैसे कार्यों के लिए बेहतरीन है। एक बार जब आप SUMIFS() के साथ सहज हो जाते हैं, तो आपको एहसास होगा कि यह समय बचाता है और बिना जटिल उपायों के आपके डेटा में महत्वपूर्ण रुझान दिखाता है।
मैं एक कंटेंट स्ट्रैटेजिस्ट हूँ जिसे जटिल विषयों को सरल बनाना पसंद है। मैंने Splunk, Hackernoon, और Tiiny Host जैसी कंपनियों को अपने दर्शकों के लिए आकर्षक और सूचनात्मक सामग्री बनाने में मदद की है।
SUMIFS() FAQs
मैं अपने डेटासेट से किसी विशिष्ट मानदंड को कैसे बाहर करूँ?⌄
विशिष्ट मानदंड को बाहर रखने के लिए <> ऑपरेटर का इस तरह उपयोग करें:
%22%3E%3Cpath%20d%3D%22m-369%200h408v79h-408z%22%20fill%3D%22%237933ff%22%2F%3E%3Cpath%20d%3D%22m-148%200h306v45h-306z%22%20fill%3D%22%23974dff%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
%22%2F%3E%3C%2FclipPath%3E%3Cg%20clip-path%3D%22url(%23a)%22%3E%3Cpath%20d%3D%22m76%200h484v36h-484z%22%20fill%3D%22%23974dff%22%2F%3E%3Cpath%20d%3D%22m0%200h384v24h-384z%22%20fill%3D%22%2306bdfc%22%20transform%3D%22matrix(1%200%200%20-1%200%2060)%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
Excel में
SUMIFS()फ़ंक्शन कई शर्तों के आधार पर संख्याएँ जोड़ता है, जिससे हमें गणनाओं पर सटीक नियंत्रण मिलता है।SUMIF()के विपरीत, जो एक शर्त की जाँच करता है,SUMIFS()एक साथ कई शर्तों को संभालता है।इस लेख में, मैं आपको उन्नत उदाहरणों और समस्या निवारण सुझावों के साथ
SUMIFS()का उपयोग करना दिखाऊँगा। अंत तक, आप जान जाएँगे कि अपनी गणनाओं को अधिक सटीक और कुशल बनाने के लिएSUMIFS()का कैसे उपयोग करें।त्वरित उत्तर
Excel में
SUMIFS()फ़ंक्शन का उपयोग करने के लिए:फ़ंक्शन शुरू करने के लिए
=SUMIFS(टाइप करें।वह रेंज चुनें जिसमें जोड़ने के लिए मान हैं।
वह रेंज चुनें जहाँ शर्त लागू होगी।
मिलान करने के लिए शर्त दर्ज करें।
कोष्ठक बंद करें और Enter दबाएँ।
यहाँ, मैंने उन सभी बिक्री का योग किया है जो 200 से अधिक हैं।
SUMIFS() का उपयोग करके मानों का योग करें। चित्र: लेखक।
SUMIFS() फ़ंक्शन को समझना
Excel में
SUMIFS()फ़ंक्शन केवल तब मानों का योग करता है जब सभी निर्दिष्ट शर्तें पूरी होती हैं। यह बड़ा>, छोटा<, बराबर=, और असमान<>जैसे तर्कसंगत ऑपरेटरों के साथ-साथ आंशिक मिलान के लिए वाइल्डकार्ड का समर्थन करता है। यह फ़ंक्शन संख्याओं, पाठ और तिथियों के साथ काम करता है।SUMIFS() का वाक्यविन्यास
SUMIFS()फ़ंक्शन पहले जोड़ने के लिए मानों की रेंज तय करता है, उसके बाद शर्त वाली रेंज और मानदंड की जोड़ियाँ आती हैं। ध्यान दें किSUMIFS()एक से अधिक तर्कसंगत शर्तों को संभाल सकता है।यहाँ:
sum_rangeवे कोशिकाएँ हैं जिनका आप योग करना चाहते हैं।criteria_range1पहली रेंज है जहाँ हम शर्त जाँचते हैं।criteria1वह शर्त है जोcriteria_range1पर लागू होती है।[criteria_range2, criteria2], …वैकल्पिक हैं। आप और रेंज-शर्त की जोड़ियाँ जोड़ सकते हैं। हर जोड़ी एक अतिरिक्त शर्त जोड़ती है जिसे पूरा होना चाहिए।ध्यान दें कि
criteria_range(वह रेंज जहाँ शर्तें जाँची जाती हैं) में पंक्तियों और स्तंभों की संख्याsum_range(जिस रेंज का योग हो रहा है) के समान होनी चाहिए।SUMIFS() उदाहरण
आइए एक बुनियादी उदाहरण आज़माते हैं। मेरे पास फलों का एक डेटासेट है जिसमें उनकी मात्रा और बिक्री है। और मैं उन Apples की कुल बिक्री जानना चाहता/चाहती हूँ जहाँ Quantity 5 से अधिक है। तो मेरा फ़ॉर्मूला होगा:
यहाँ
C2:C8जोड़ने के लिए मानों की रेंज है(Sales ($))।A2:A8, "Apples"वह शर्त है जिससे केवल वे पंक्तियाँ शामिल हों जहाँ उत्पाद Apples है। औरB2:B8, ">5"वह शर्त है जिससे केवल वे पंक्तियाँ शामिल हों जहाँ Quantity 5 से अधिक है।शर्त पर बिक्री का योग करने के लिए SUMIFS() फ़ॉर्मूला। चित्र: लेखक।
SUMIF() बनाम SUMIFS()
SUMIF()औरSUMIFS()के बीच मुख्य अंतर शर्तों की संख्या है।SUMIF()एकल शर्त संभालता है। जबकिSUMIFS()कई शर्तों को संभाल सकता है।=SUMIF(range, criteria, [sum_range])=SUMIFS(sum_range, criteria_range1, criteria1, …)इसे दूसरे तरीके से ऐसे समझें कि
SUMIFS(),SUMIF()का विस्तारित रूप है। मैं ऐसा इसलिए कहता/कहती हूँ क्योंकिSUMIFS()एक ही शर्त को भी संभाल सकता है, इसलिए यदि आपSUMIFS()को केवल एक शर्त के साथ उपयोग करते हैं, तो यहSUMIF()की तरह ही काम करता है। इसका मतलब है किSUMIF()की जगहSUMIFS()चुनना तकनीकी रूप से कभी गलत नहीं है। आप हमारे Excel फ़ॉर्मूला चीट शीट देख सकते हैं ताकि और उपयोगी Excel फ़ॉर्मूलों का पता लगा सकें।Excel में SUMIFS() के उपयोग के मामले
अब जबकि आप जानते हैं कि
SUMIFS()कैसे काम करता है, आइए कुछ उन्नत मामले देखें जहाँ हम इसे वास्तविक दुनिया में उपयोग कर सकते हैं।विभिन्न डेटा प्रकारों के साथ SUMIFS() का उपयोग
SUMIFS()संख्याएँ, पाठ, और तिथियों जैसे विभिन्न डेटा प्रकारों के साथ काम कर सकता है। मान लें मेरे पास बिक्री रिपोर्ट है, लेकिन कुछ पंक्तियों में ग्राहक के नाम गायब हैं। और मैं केवल उन्हीं का योग करना चाहता/चाहती हूँ जहाँ ग्राहक का नाम मौजूद है। इसके लिए, मैं निम्न फ़ॉर्मूला उपयोग करता/करती हूँ:यह फ़ॉर्मूला Customer कॉलम को देखता है, गैर-खाली कोशिकाओं की जाँच करता है, और फिर संबंधित बिक्री का योग करता है।
SUMIFS() फ़ंक्शन से सभी गैर-खाली कोशिकाओं का योग करें। चित्र: लेखक।
जहाँ ग्राहक के नाम गायब हैं, वहाँ की बिक्री का योग करने के लिए, मैं यह फ़ॉर्मूला उपयोग करता/करती हूँ:
SUMIFS() फ़ंक्शन से खाली कोशिकाओं का योग करें। चित्र: लेखक।
शर्त को हार्डकोड करने की बजाय, हम उसे किसी अन्य सेल में रख सकते हैं। यदि मैं Sarah Lee के लिए बिक्री का योग करना चाहता/चाहती हूँ, तो मैं उसे E3 सेल में टाइप करता/करती हूँ और यह फ़ॉर्मूला उपयोग करता/करती हूँ:
जैसे ही E3 में मान बदलेगा, कुल अपने-आप अपडेट हो जाएगा।
सेल रेफरेंस के साथ मानों का योग करने के लिए SUMIFS() का उपयोग करें। चित्र: लेखक।
SUMIFS() में OR लॉजिक लागू करना
हम जानते हैं कि
SUMIFS()केवलANDलॉजिक का उपयोग करता है, यानी जो शर्तें हम तय करते हैं वे सभी सही होनी चाहिए तभी मान जोड़े जाएँगे। हालाँकि, यदि हमORलॉजिक चाहते हैं, जो कई शर्तों में से कोई एक पूरी होने पर भी मान जोड़ देता है, तो हम कईSUMIFS()को जोड़कर ऐसा कर सकते हैं।उदाहरण के लिए, यदि मैं John Doe या Drake White में से किसी एक की बिक्री का योग करना चाहता/चाहती हूँ, तो मैं दो
SUMIFS()फ़ॉर्मूलों को ऐसे जोड़ता/जोड़ती हूँ:क्योंकि Drake White सूची में नहीं है, फ़ॉर्मूले का वह भाग
0लौटाता है। कुल में केवल John Doe की बिक्री शामिल होती है, जो दर्शाता है किORलॉजिक कैसे काम करता है।SUMIFS() फ़ंक्शन में OR लॉजिक का उपयोग करें। चित्र: लेखक।
Excel 365 या बाद के संस्करणों में, हम इसे एक array फ़ॉर्मूला से सरल बना सकते हैं और यह वही परिणाम देगा।
SUMIFS() array फ़ॉर्मूला का उपयोग करें। चित्र: लेखक।
Named ranges के साथ SUMIFS()
हम सेल रेफरेंस जैसे
A2:A5याC2:C5की बजाय named ranges का भी उपयोग कर सकते हैं। इस उदाहरण में, मैंA2:A5चुनकर उसेCustomer_listनाम देता/देती हूँ औरC2:C5चुनकर उसेSales_listनाम देता/देती हूँ. अब John की कुल बिक्री खोजने के लिए John, मैं यह फ़ॉर्मूला उपयोग करता/करती हूँ:Named ranges के साथ SUMIFS() से मानों का योग करें। चित्र: लेखक।
आंशिक मिलान के लिए वाइल्डकार्ड का उपयोग
वाइल्डकार्ड विशेष अक्षर (
*या?) होते हैं जो पाठ खोज और पैटर्न मिलान में अज्ञात या परिवर्तनीय अक्षरों का प्रतिनिधित्व करते हैं:*किसी भी संख्या के अक्षरों से मेल खाता है (शून्य सहित)।?ठीक एक अक्षर से मेल खाता है।यह ऐसे काम करते हैं:
A*उन सभी कोशिकाओं से मेल खाएगा जो A से शुरू होती हैं।*Aउन सभी कोशिकाओं से मेल खाएगा जो A पर समाप्त होती हैं।*A*उन सभी कोशिकाओं से मेल खाएगा जिनमें कहीं भी A शामिल है।SUMIFS()फ़ंक्शन वाइल्डकार्ड अक्षरों के माध्यम से पाठ-मिलान की क्षमता प्रदान करता है। यह उन मानों की पहचान करता है जो किसी विशिष्ट पाठ पैटर्न से शुरू, समाप्त, या उसे समाहित करते हैं। मान लें मैं उस Product Code के लिए बिक्री का योग करना चाहता/चाहती हूँ जो ABC से शुरू होता है। इस स्थिति में, मेरा फ़ॉर्मूला ऐसा होगा:उन बिक्री का योग करने के लिए जो
00पर समाप्त होती हैं, मैं लिखता/लिखती हूँ:SUMIFS() में वाइल्डकार्ड का उपयोग करें। चित्र: लेखक।
तिथि सीमाओं के आधार पर योग करना
SUMIFS()किसी विशिष्ट तिथि सीमा के भीतर के मानों का भी योग कर सकता है। यदि मैं जनवरी 2023 की सारी बिक्री जानना चाहता/चाहती हूँ, तो मैं यह फ़ॉर्मूला उपयोग करता/करती हूँ:यह फ़ॉर्मूला 1 जनवरी 2023 से 31 जनवरी 2023 तक की तिथियों की जाँच करेगा और फिर सारी बिक्री का योग करेगा।
SUMIFS() का उपयोग करके तिथि सीमा का योग करें। चित्र: लेखक।
एकाधिक संख्यात्मक शर्तों के साथ मानों का योग
SUMIFS()फ़ंक्शन उन मानों का योग कर सकता है जो विशिष्ट संख्यात्मक सीमाओं में आते हैं। यहाँ, मेरे पास एक रैंडम डेटासेट है और मैं उन मानों का योग करना चाहता/चाहती हूँ जहाँ Sales 100 से अधिक लेकिन 300 से कम है। इसके लिए, मैं निम्न फ़ॉर्मूला उपयोग करता/करती हूँ:यह फ़ॉर्मूला पहले पहले argument के रूप में जोड़ने के लिए कोशिकाओं की रेंज (
C2:C5) बताता है। फिर, यह उसी रेंज पर उपर्युक्त दो शर्तें लागू करता है।SUMIFS()फ़ंक्शन रेंज की प्रत्येक कोशिका का दोनों मानदंडों के विरुद्ध एक साथ मूल्यांकन करता है। और आप देख सकते हैं कि केवल वे कोशिकाएँ जिनके मान 100 और 300 के बीच हैं (exclusive) योग में शामिल होती हैं।SUMIFS() का उपयोग करके संख्या सीमा के भीतर मानों का योग करें। चित्र: लेखक।
यदि SUMIFS() काम न करे तो समस्या निवारण
यदि आपका
SUMIFS()फ़ंक्शन अपेक्षा के अनुसार काम नहीं कर रहा है, तो निम्न समस्याओं की जाँच करें:रेंज आकार का मेल न होना
यदि आपका
SUMIFS()फ़ॉर्मूला त्रुटि देता है, तो इसका कारण रेंज आकार का मेल न होना हो सकता है।SUMIFS()में, आपके द्वारा चुनी गई सभी रेंज, जैसेsum_rangeऔरcriteria_ranges, में पंक्तियों और स्तंभों की संख्या समान होनी चाहिए।उदाहरण के लिए, इस फ़ॉर्मूले में sum range
B2:B8है, लेकिन criteria rangeA2:A7है, जिसमें एक पंक्ति कम है:यह असंगति
#VALUE!त्रुटि देगी। इसलिए, सुनिश्चित करें कि सभी रेंज समान आकार की हों, जैसे:पाठ मानदंड का गलत फ़ॉर्मैट
SUMIFS()में, पाठ मानदंड को उद्धरण चिह्नों में रखना अनिवार्य है। यदि आप ऐसा नहीं करते, तो यह गलत परिणाम दे सकता है। उदाहरण के लिए, यह फ़ॉर्मूला0लौटाएगा क्योंकि Excel Drake White को पाठ नहीं बल्कि नाम या चर के रूप में देखता है:इसे ठीक करने के लिए, Drake White को ऐसे उद्धरण चिह्नों में रखें और सही आउटपुट मिलेगा:
अनपेक्षित शून्य मान
यदि फ़ॉर्मूला आपके अपेक्षित परिणाम की जगह
0लौटाता है, तो यह संख्यात्मक या तिथि-आधारित शर्तों के गलत फ़ॉर्मैट के कारण हो सकता है।SUMIFS()में,>100या<01-Jan-2023जैसी शर्तें उद्धरण चिह्नों में पाठ स्ट्रिंग के रूप में लिखी जानी चाहिए। अन्यथा, यह त्रुटि या0लौटा सकता है क्योंकि शर्त उद्धरण में नहीं है।या हम तिथि को किसी अलग सेल (जैसे
C1) में रख सकते हैं और यह उपयोग कर सकते हैं:array transformations के साथ SUMIFS() काम न करना
SUMIFS()कुछ रूपांतरों का समर्थन नहीं करता, जैसे फ़ॉर्मूला के भीतर डेटा से विशिष्ट घटक निकालना। उदाहरण के लिए, यदि मैं जनवरी और मार्च की बिक्री का योग करना चाहता/चाहती हूँ, तोSUMIFS()सीधे {"January","March"} जैसी array criteria के साथ काम नहीं करेगा।इसे दिखाने के लिए, मान लें मेरे पास कॉलम A में महीने (January, February, March, April) और कॉलम B में बिक्री मान (100, 200, 300, 400) हैं। फ़ॉर्मूला
अपेक्षित योग 400 (100 + 300) नहीं लौटाएगा। इसके बजाय, हम या तो
SUMPRODUCT()का उपयोग कर सकते हैं:या इस फ़ॉर्मूले के साथ
SUMIFS()कोSUM()में रैप कर सकते हैं:दोनों समाधान array criteria को सही ढंग से संभालेंगे और जनवरी तथा मार्च की बिक्री का योग लौटाएँगे।
SUMIFS() का उपयोग करते समय सर्वोत्तम अभ्यास
SUMIFS()फ़ंक्शन का अधिकतम लाभ उठाने के लिए, इन सर्वोत्तम अभ्यासों का पालन कर आम गलतियों से बचें:लचीले फ़िल्टरिंग के लिए वाइल्डकार्ड का उपयोग करें: डेटा अक्सर पूरी तरह व्यवस्थित नहीं होता। हो सकता है उत्पाद नाम हमेशा एक जैसे न लिखे हों, या अतिरिक्त स्पेस हों। वाइल्डकार्ड आपको सटीक पाठ की जरूरत के बिना पैटर्न मिलाने में मदद करते हैं।
मानदंड हार्डकोड करने से बचें: शर्तों को सीधे फ़ॉर्मूला में टाइप करने के बजाय, उन्हें अलग सेल में रखें। यदि आप "Drake White" या ">100" जैसी शर्त हार्डकोड करते हैं, तो हर बार बदलाव के लिए फ़ॉर्मूला संपादित करना होगा। जबकि सेल रेफरेंस के साथ, फ़ॉर्मूला अपने-आप अपडेट हो जाएगा।
फ़ॉर्मूले कॉपी करते समय absolute references का उपयोग करें: जब आप
SUMIFS()फ़ॉर्मूला अन्य कोशिकाओं में कॉपी करते हैं, तो Excel स्वतः रेफरेंस बदल देता है। यदि आप ऐसा नहीं चाहते, तो कोशिकाओं को लॉक करने के लिए$जोड़कर absolute references का उपयोग करें।अंतिम विचार
SUMIFS()एक Excel फ़ंक्शन है जो विशिष्ट मानदंडों से मेल खाते संख्याओं को फ़िल्टर करके जोड़ने में हमारी मदद करता है। यह क्षेत्रवार बिक्री ट्रैक करने, श्रेणीवार खर्चों का सारांश बनाने, या उत्पाद प्रकार के अनुसार इन्वेंट्री गिनने जैसे कार्यों के लिए बेहतरीन है। एक बार जब आपSUMIFS()के साथ सहज हो जाते हैं, तो आपको एहसास होगा कि यह समय बचाता है और बिना जटिल उपायों के आपके डेटा में महत्वपूर्ण रुझान दिखाता है।यदि आप इन कौशलों पर आगे काम करना चाहते हैं, तो हमारे Data Analysis in Excel कोर्स, Data Preparation in Excel कोर्स, और Data Visualization in Excel कोर्स या Excel Fundamentals स्किल ट्रैक देखें। ये आपके मौजूदा कौशलों को निखारने में मदद करेंगे।