एक्सेल सूत्र: समीपस्थ मानदंड पर आधारित योग कॉलम -

सामान्य सूत्र

=SUMPRODUCT(--(range1=criteria),range2)

सारांश

आसन्न कॉलम में मापदंड के आधार पर कॉलम को योग या उप-योग करने के लिए, आप SUMPRODUCT फ़ंक्शन के आधार पर एक सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, J5 में सूत्र है:

=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)

स्पष्टीकरण

मूल में, यह सूत्र दो सरणियों के गुणनफल के लिए SUMPRODUCT का उपयोग करता है: array1 और array2 । पहला मान , array1 मानदंड को पूरा करने वाले केवल मानों को अनुमति देने के लिए "फ़िल्टर" के रूप में कार्य करने के लिए सेट किया गया है।

Array1 एक श्रेणी का उपयोग करता है जो पहले कॉलम पर शुरू होता है जिसमें मान होते हैं जो मानदंड पास करना चाहिए। ये "मानदंड मान", "डेटा मान" के निकट और तुरंत बगल के कॉलम में बैठते हैं।

मानदंड को एक साधारण परीक्षण के रूप में लागू किया जाता है जो TRUE और FALSE मूल्यों की एक सरणी बनाता है:

--($B5:$H5=J$4)

आपूर्ति की गई मानदंड का उपयोग करते हुए पहले सरणी में प्रत्येक बिट फॉर्मूला "परीक्षण" करता है, फिर परिणामी TRUE और FALSE मूल्यों को 1 और 0 के बराबर करने के लिए एक दोहरे-नकारात्मक (-) का उपयोग करता है। परिणाम इस तरह दिखता है:

(1,0,0,0,1,0,1)

ध्यान दें कि 1s कॉलम 1,5, और 7 के अनुरूप है, जो "ए" के मानदंडों को पूरा करता है।

के लिए array2 SUMPRODUCT के अंदर है, हम एक सीमा है कि है सही करने के लिए एक स्तंभ द्वारा "स्थानांतरित" का उपयोग करें। यह श्रेणी पहले कॉलम से शुरू होती है जिसमें योग होते हैं और अंतिम कॉलम के साथ समाप्त होता है जिसमें योग होते हैं।

इसलिए, जे 5 में उदाहरण सूत्र में, सरणियों के आबाद होने के बाद, हमारे पास है:

=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))

चूंकि SUMPRODUCT को विशेष रूप से उन त्रुटियों की अनदेखी करने के लिए क्रमादेशित किया जाता है, जो पाठ मानों को गुणा करने के परिणामस्वरूप होती हैं, अंतिम सरणी इस प्रकार दिखती है:

(1,0,0,0,1,0,1)

केवल मान जो "जीवित" गुणा हैं, वे हैं जो array1 के अंदर 1s के अनुरूप हैं । आप में तर्क के बारे में सोच सकते array1 में "छानने" मूल्यों array2 ।