उदाहरण कार्यपुस्तिका डाउनलोड करें
यह ट्यूटोरियल प्रदर्शित करेगा कि एक्सेल और गूगल शीट्स में विशिष्ट महीनों के अनुरूप डेटा को समेटने के लिए SUMIFS फ़ंक्शन का उपयोग कैसे करें।
योग यदि माह के अनुसार
सबसे पहले, हम प्रदर्शित करेंगे कि किसी विशिष्ट महीने और वर्ष में आने वाली तिथियों के अनुरूप डेटा का योग कैसे किया जाता है।
हम SUMIFS फंक्शन का उपयोग DATE, YEAR, MONTH और EOMONTH फंक्शन्स के साथ योग करने के लिए कर सकते हैं बिक्री की संख्या प्रत्येक के भीतर महीना।
| 1 | =SUMIFS(C3:C9,B3:B9,">="&DATE(YEAR(E3),MONTH(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
उपरोक्त सूत्र बनाने के लिए, हम प्रत्येक माह के लिए दिनांक सीमा निर्धारित करके प्रारंभ करते हैं. हम महीने के पहले दिन (उदा. 5/1/2021) को परिभाषित करने के लिए DATE फ़ंक्शन का उपयोग करते हैं। हम इसे सूत्र में दिनांक को "हार्ड-कोडिंग" करके कर सकते हैं:
| 1 | ">="और दिनांक(२०२१,५,१) |
या, बेहतर अभी तक, हम सेल E3 के भीतर एक तारीख का हवाला देकर मानदंड को लचीला बना सकते हैं, तारीख का साल और महीना निकाल सकते हैं (और दिन को 1 के बराबर सेट कर सकते हैं), जैसा कि ऊपर दिए गए उदाहरण में दिखाया गया है:
| 1 | ">=" और दिनांक (वर्ष (ई 3), माह (ई 3), 1) |
महीने के अंतिम दिन को परिभाषित करने के लिए, हम EOMONTH फ़ंक्शन का उपयोग कर सकते हैं:
| 1 | "<="&EOMONTH(E3,0) |
इन सभी मानदंडों को एक साथ रखकर, हम निम्नलिखित SUMIFS सूत्र लिख सकते हैं:
| 1 | =SUMIFS(C3:C9,B3:B9,">="&DATE(YEAR(E3),MONTH(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
लॉकिंग सेल संदर्भ
हमारे सूत्रों को पढ़ने में आसान बनाने के लिए, हमने लॉक किए गए सेल संदर्भों के बिना सूत्र दिखाए हैं:
| 1 | =SUMIFS(C3:C9,B3:B9,">="&DATE(YEAR(E3),MONTH(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
लेकिन आपकी फ़ाइल में कहीं और कॉपी और पेस्ट करने पर ये सूत्र ठीक से काम नहीं करेंगे। इसके बजाय, आपको लॉक किए गए सेल संदर्भों का उपयोग इस तरह करना चाहिए:
| 1 | =SUMIFS($C$3:$C$9,$B$3:$B$9,">="&DATE(YEAR(E3),MONTH(E3),1),$B$3:$B$9,"<=" &EOMONTH(E3,0)) |
अधिक जानने के लिए लॉकिंग सेल संदर्भों पर हमारा लेख पढ़ें।
स्वरूपण माह मान
इस उदाहरण में, हमने कॉलम ई में महीनों को सूचीबद्ध किया है। ये महीने मान वास्तव में कस्टम नंबर स्वरूपण का उपयोग करके दिन को छोड़ने के लिए स्वरूपित दिनांक हैं।
मई 2022 को दिखाने के लिए कस्टम डेटा प्रारूप "mm yyyy" है।
कई वर्षों में महीने के हिसाब से योग
ऊपर दिए गए उदाहरण में डेटा को उन तारीखों के साथ सारांशित किया गया है जो एक विशिष्ट महीने और वर्ष के भीतर आती हैं। इसके बजाय आप SUMPRODUCT फ़ंक्शन का उपयोग करके किसी भी वर्ष में एक महीने के भीतर आने वाली तिथियों के साथ डेटा जोड़ सकते हैं।
| 1 | =SUMPRODUCT(C3:C8,--(MONTH(B3:B8)=MONTH(G3))) |
इस उदाहरण में, हम जटिल "योग अगर" गणना करने के लिए SUMPRODUCT फ़ंक्शन का उपयोग करते हैं। आइए सूत्र के माध्यम से चलते हैं।
यह हमारा अंतिम सूत्र है:
| 1 | =SUMPRODUCT(C3:C8,--(MONTH(B3:B8)=MONTH(G3))) |
सबसे पहले, SUMPRODUCT फ़ंक्शन सूचीबद्ध करता है बिक्री की संख्या प्रत्येक के लिए बिक्री की तारीख और फिर प्रत्येक के महीने की तुलना करता है बिक्री की तारीख निर्दिष्ट के खिलाफ महीना, यदि महीने मेल खाते हैं तो TRUE लौटाते हैं, या FALSE यदि वे नहीं देते हैं:
| 1 | =SUMPRODUCT({30;42;51;28;17;34},--({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE})) |
आगे डबल डैश (-) TRUE और FALSE मानों को 1s और 0s में परिवर्तित करते हैं:
| 1 | =SUMPRODUCT({30;42;51;28;17;34},{1;0;1;0;1;0}) |
SUMPRODUCT फ़ंक्शन तब सरणियों में प्रविष्टियों की प्रत्येक जोड़ी को गुणा करता है:
| 1 | =SUMPRODUCT({30;0;51;0;17;0}) |
अंत में, सरणी में संख्याओं को संक्षेप में प्रस्तुत किया गया है:
| 1 | =98 |
SUMPRODUCT फ़ंक्शन में बूलियन स्टेटमेंट और "-" कमांड का उपयोग करने के बारे में अधिक विवरण यहां पाया जा सकता है
Google पत्रक में अगर महीने के हिसाब से योग करें
ये सूत्र ठीक उसी तरह काम करते हैं जैसे Google पत्रक में Excel में होता है।
