सामान्य सूत्र
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
सारांश
केवल मानदंड के साथ दृश्यमान पंक्तियों को गिनने के लिए, आप SUMPRODUCT, SUBTOTAL, और FFSET के आधार पर एक जटिल विकल्प का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, C12 में सूत्र है:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
प्रस्तावना
SUBTOTAL फ़ंक्शन आसानी से छिपी और गैर-छिपी पंक्तियों के लिए रकम और मायने रखता है। हालांकि, यह कुछ मदद के बिना COUNTIF या SUMIF जैसे मानदंडों को संभालने में सक्षम नहीं है। एक समाधान सबटोटल फ़ंक्शन (OFFSET के माध्यम से) और मानदंड को लागू करने के लिए SUMPRODUCT का उपयोग करना है। इस दृष्टिकोण का विवरण नीचे वर्णित है।
स्पष्टीकरण
मूल में, यह सूत्र SUMPRODUCT के अंदर दो सरणियों को सेट करके काम करता है। पहला सरणी मापदंड लागू करता है, और दूसरा सरणी दृश्यता को संभालता है:
=SUMPRODUCT(criteria*visibility)
मापदंड सूत्र के भाग के साथ लागू किया जाता है:
=(C5:C8=C10)
जो इस तरह एक सरणी उत्पन्न करता है:
(FALSE;TRUE;FALSE;TRUE)
जहाँ TRUE का अर्थ है "मानदंड पूरा करना"। ध्यान दें क्योंकि हम इस सरणी पर गुणन (*) का उपयोग कर रहे हैं, TRUE FALSE मान स्वचालित रूप से गणित के ऑपरेशन द्वारा 1 और 0 में परिवर्तित हो जाएंगे, इसलिए हम इसके साथ समाप्त होते हैं:
(0;1;0;1)
फंक्शन नंबर 103 के साथ, SUBTOTAL का उपयोग करके दृश्यता फ़िल्टर लागू किया जाता है।
SUBTOTAL गणनाओं को चलाने के दौरान छिपी हुई पंक्तियों को बाहर करने में सक्षम है, इसलिए हम इस मामले में SUMPRODUCT के अंदर छिपी पंक्तियों को बाहर करने के लिए "फ़िल्टर" उत्पन्न करने के लिए इसका उपयोग कर सकते हैं। हालांकि समस्या यह है कि SUBTOTAL एकल नंबर देता है, जबकि हमें SUMPRODUCT के अंदर इसे सफलतापूर्वक उपयोग करने के लिए परिणामों की एक सरणी की आवश्यकता होती है। चाल का उपयोग OFFSET को प्रति पंक्ति एक संदर्भ में फीड करने के लिए करना है, ताकि OFFSET प्रति पंक्ति एक परिणाम लौटाए।
बेशक, इसके लिए एक और ट्रिक की आवश्यकता होती है, जो कि ओएफएसईटीईटी को एक एरियर देना है जिसमें प्रति पंक्ति एक नंबर होता है, जिसकी शुरुआत शून्य से होती है। हम ROW फ़ंक्शन पर निर्मित अभिव्यक्ति के साथ ऐसा करते हैं:
=ROW(C5:C8)-MIN(ROW(C5:C8)
जो इस तरह एक सरणी उत्पन्न करेगा:
(0;1;2;3)
सारांश में, दूसरा एरे (जो SUBTOTAL का उपयोग करके दृश्यता को संभालता है), इस तरह से उत्पन्न होता है:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
और, आखिरकार, हमारे पास:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
जो 1 लौटाता है।
कई मापदंड
आप इस तरह से कई मानदंडों को संभालने के लिए सूत्र का विस्तार कर सकते हैं:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
परिणाम प्राप्त करना
किसी संख्या के बजाय मानों को वापस करने के लिए, आप योग सीमा को शामिल करने के लिए सूत्र को अनुकूलित कर सकते हैं:
=SUMPRODUCT(criteria*visibility*sumrange)
मापदंड और दृश्यता सरणियाँ ऊपर बताए गए अनुसार काम करती हैं, उन कोशिकाओं को छोड़कर जो दिखाई नहीं देती हैं। यदि आपको आंशिक मिलान की आवश्यकता है, तो आप ISNUMBER + SEARCH का उपयोग करके एक अभिव्यक्ति का निर्माण कर सकते हैं, जैसा कि यहाँ बताया गया है।
