सामान्य सूत्र
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
सारांश
30 मिनट से अधिक की कुल राशि का योग करने के लिए, अवधि का प्रतिनिधित्व करने वाले कई बार सेट करके, आप SUMPRODUCT और TIME फ़ंक्शन का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, G5 में सूत्र है:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
जहाँ "समय" नाम की श्रेणी C5: C14 है।
स्पष्टीकरण
यह सूत्र सरणियों को उत्पन्न करने वाले दो भावों के परिणाम को योग करने के लिए SUMPRODUCT फ़ंक्शन का उपयोग करता है। लक्ष्य केवल 30 मिनट से अधिक समय का योग है, "अतिरिक्त" या "अतिरिक्त" समय। पहली अभिव्यक्ति हर बार नामित श्रेणी "समय" से 30 मिनट घटाती है:
times-TIME(0,30,0)
इसका परिणाम इस प्रकार है:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
दूसरी अभिव्यक्ति 30 मिनट से अधिक समय के लिए एक तार्किक परीक्षण है:
times>TIME(0,30,0)
यह TRUE FALSE मूल्यों की एक सरणी बनाता है:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
SUMPRODUCT के अंदर, इस सरणी को बनाने के लिए इन दो सरणियों को एक साथ गुणा किया जाता है:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
पहले सरणी में नकारात्मक मान देखें अब शून्य हैं। गुणन के दौरान, TRUE FALSE मान 1 और शून्य में परिवर्तित हो जाते हैं, इसलिए FALSE मान 30 बार से अधिक नहीं "रद्द" होता है। अंत में, SUMPRODUCT सरणी में सभी मानों का योग, 1 घंटा और 4 मिनट (1:04) देता है।
SUMIFS और COUNTIFS के साथ वैकल्पिक
अपने आप में, SUMIFS समय मानों के डेल्टा को 30 मिनट से अधिक नहीं जोड़ सकता है। उपर्युक्त SUMPRODUCT के समान परिणाम प्राप्त करने के लिए SUMIFS और COUNTIFS का एक साथ उपयोग किया जा सकता है:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
24 घंटे से अधिक समय
यदि कुल समय 24 घंटे से अधिक हो सकता है, तो इस तरह से कस्टम समय प्रारूप का उपयोग करें:
(h):mm:ss
चौकोर ब्रैकेट सिंटैक्स एक्सेल को 24 घंटे से अधिक "रोल ओवर" बार नहीं करने के लिए कहता है।
एक सहायक स्तंभ के साथ
जैसा कि उदाहरण में दिखाया गया है, आप डेल्टास की गणना और योग करने के लिए एक सहायक कॉलम भी जोड़ सकते हैं। D5 में सूत्र, नीचे कॉपी किया गया है:
=MAX(C5-"00:30",0)
यहां, MAX का उपयोग नकारात्मक समय डेल्टास से छुटकारा पाने के लिए किया जाता है, जो स्तंभ C में 30 मिनट से कम समय के कारण होता है। ध्यान दें कि D15 में परिणाम G5 में परिणाम के समान है।
