Excel में SUMPRODUCT INDEX MATCH formula से Dynamic Sales Report कैसे बनाएं – Hindi Tutorial

SUMPRODUCT INDEX MATCH formula Hindi ट्यूटोरियल: दोस्तों Excel में काम करते वक्त अक्सर एक ऐसी situation आती है जहाँ data में एक ही product का नाम कई-कई बार लिखा होता है — और हमें किसी एक खास महीने या criteria के हिसाब से उनका total निकालना होता है। अगर आप अब तक यह काम manually करते आए हैं, तो आज का यह tutorial आपके लिए एक game-changer साबित होगा।

दरअसल, हमने पहले इसी data पर एक video बनाया था जिसमें हमने formula दिखाया था। उस video को देखने के बाद बहुत सारे viewers ने comment किया कि “इस formula को step-by-step detail में explain करो।” तो आज यही करने वाले हैं — SUMPRODUCT के साथ INDEX और MATCH का पूरा combination, बिलकुल शुरू से।

पहले — पुरानी Problem को समझें

अगर आपने हमारा पिछला video नहीं देखा, तो एक बार ज़रूर देख लीजिए। इससे आपको पता चलेगा कि हम किस problem को solve करने की कोशिश कर रहे हैं:

आज का Tutorial — Formula की पूरी Explanation

यहाँ है वो video जिसमें मैंने पूरे formula को detail में explain किया गया है। Video देखें, फिर नीचे written guide follow करें:

📥 Practice File Download करें

सिर्फ पढ़ने से बात नहीं बनेगी — खुद file खोलकर formula try करें। नीचे वही file है जो इस tutorial में use किया है:

📥 Download Excel Practice File

(Free Download | .xlsx Format)

समस्या क्या है? (The Problem)

हमारे पास एक Sales Data है जिसमें 5 products हैं — Pen, Notebook, Pencil, Eraser और Marker। लेकिन यहाँ दिक्कत यह है कि हर product का नाम data में 4-4 बार repeat हुआ है। साथ ही महीनों के अलग-अलग columns हैं — Jan, Feb और Mar।

हमें यह करना है:

• किसी एक product को चुनें (जैसे Marker)
• किसी एक महीने को चुनें (जैसे Jan)
• उस product की उस महीने की सभी entries का total एक formula से निकालें
• और यह formula dynamic हो — product या month बदलते ही result automatically update हो जाए

अगर हम सिर्फ SUMIF use करें, तो वह एक fixed column के हिसाब से काम करेगा। और अगर VLOOKUP use करें, तो वह सिर्फ पहली matching entry देगा। इसीलिए हमें एक smarter formula चाहिए।

यही वजह है कि हम SUMPRODUCT + INDEX + MATCH का combination use करेंगे — यह तीनों functions मिलकर वो काम करते हैं जो अकेले कोई एक function नहीं कर सकता।

Data का Structure समझें — शुरू से बनाएं

पहले अपनी Excel file में बिलकुल वैसा ही data तैयार करें जैसा practice file में है। यह structure ध्यान से देखें:

Part 1 — Criteria Input Area (ऊपर)

Criteria Input के लिए मैंने एक छोटा टेबल बनाया है जो इस प्रकार दिखाता है👇

Part 2 — Sales Data Table (नीचे, B7 से शुरू)

मैंने जो excel फाइल दिया है उसमे data table कुछ इस प्रकार है👇

ध्यान दें: Row 7 में headers हैं (Product, Jan, Feb, Mar) और Row 8 से Row 27 तक 20 rows का data है। हर product 4 बार repeat हुआ है।

Formula को Step-by-Step समझें

यहाँ तक आप पहुँच गए है तो आपका data तो तैयार हो गया। अब असली काम — formula। हम D4 cell में यह formula लिखेंगे:

=SUMPRODUCT((B8:B27=C2)*INDEX(C8:E27,0,MATCH(C3,C7:E7,0)))

यह formula थोड़ा लंबा दिखता है, लेकिन इसे तोड़कर देखें तो बेहद simple है। चलिए एक-एक हिस्से को समझते हैं।

Part 1 — Condition Array: (B8:B27=C2)

(B8:B27=C2)

यह formula का पहला और सबसे ज़रूरी हिस्सा है।

• B8:B27 = हमारा Product column (सभी 20 rows)
• C2 = वह cell जिसमें हमने product का नाम लिखा है (जैसे “Marker”)
• यह expression Excel में एक array बनाता है — जहाँ-जहाँ product match होता है वहाँ TRUE (1), बाकी जगह FALSE (0)

उदाहरण के लिए अगर C2 में “Marker” है, तो जिन rows में Marker है (B12, B17, B22, B27) वहाँ 1 आएगा और बाकी सब जगह 0:

{0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1}
 ↑Pen      ↑Marker    ↑Marker    ↑Marker    ↑Marker

ध्यान दें: अगर आप Excel में Data Entry Form बनाना चाहते है वो भी fully automatic तो ये पढे: Excel Data Entry Form VBA: सिंगल क्लिक में ऑटोमैटिक डेटा सेव करना सीखें!

Part 2 — Dynamic Column Selection: INDEX(C8:E27, 0, MATCH(C3, C7:E7, 0))

INDEX(C8:E27, 0, MATCH(C3, C7:E7, 0))

यह formula का दूसरा हिस्सा है जो dynamically सही महीने का column select करता है। इसे और तोड़कर देखते हैं:

अंदर का MATCH पहले काम करता है:

MATCH(C3, C7:E7, 0)

• C3 = जो month हमने चुना है (जैसे “Jan”)
• C7:E7 = header row जिसमें Jan, Feb, Mar लिखे हैं
• 0 = Exact match
• अगर C3 में “Jan” है तो MATCH return करेगा 1 (क्योंकि Jan पहला है), “Feb” है तो 2, “Mar” है तो 3

फिर INDEX उस column को return करता है:

• C8:E27 = पूरा sales data (तीनों महीनों के सभी values)
• Row number = 0 दिया है — इसका मतलब INDEX पूरा column return करेगा, सिर्फ एक cell नहीं
• Column number = MATCH से मिला number (1, 2 या 3)

यानी अगर Jan select है, तो INDEX पूरा Jan column (C8:C27) एक array के रूप में return करेगा।

💡 यह trick खास क्यों है? VLOOKUP या SUMIF में column number manually fix करना पड़ता है। लेकिन यहाँ MATCH automatically सही column number ढूंढता है — इसीलिए C3 में महीना बदलते ही पूरा result update हो जाता है।

Part 3 — सब कुछ जोड़ता है: SUMPRODUCT

SUMPRODUCT( (B8:B27=C2) * INDEX(...) )

• Part 1 से मिला array: {0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1}
• Part 2 से मिला array (Jan column के सभी values): {2500,4000,2200,2100,3500,2700,4200,2400,2300,3600,...}
• इन दोनों arrays को multiply (*) किया जाता है — जहाँ condition 0 है वहाँ value 0 हो जाती है, जहाँ 1 है वहाँ original value रहती है
• SUMPRODUCT इन सब multiplied values को जोड़ देता है

Result: 0+0+0+0+3500+0+…+3600+…+3800+…+4000 = 14,900

पूरा Formula — एक जगह

D4 में यह formula लिखें और Enter दबाएं:

=SUMPRODUCT((B8:B27=C2)*INDEX(C8:E27,0,MATCH(C3,C7:E7,0)))

Formula enter करते ही result आ जाएगा। अब C2 में product और C3 में month बदलकर देखें — result automatically update होगा।

Result Verify करें — क्या Formula सही है?

किसी भी formula को blindly trust नहीं करना चाहिए। हम manually verify करेंगे।

Example: Marker — January

Data में Marker की Jan column में ये 4 values हैं:

और हमारा formula भी D4 में 14,900 दिखाता है — बिलकुल सही! ✅

Excel में manually verify करने का तरीका (जैसा video में दिखाया):

1. C12 cell click करें
2. Ctrl दबाए रखें और C17, C22, C27 भी click करें
3. Excel के status bar (नीचे) में Sum: 14,900 दिखेगा
4. Formula का result भी 14,900 — perfect match! ✅

Dynamic Test करें

यही तो इस formula की असली ताकत है। अब C2 और C3 बदलकर देखें:

इनमें से किसी भी combination को try करें — formula हर बार सही जवाब देगा।

तीनों Functions का Quick Reference

🌟 Pro Tip: अगर आप C2 और C3 को Dropdown List बना दें (Data → Data Validation → List), तो users product और month select कर सकेंगे — typing mistake का डर भी नहीं रहेगा और dashboard और भी professional लगेगा।

यह Formula कब काम आता है?

यह combination सिर्फ इस sales example तक सीमित नहीं है। जब भी आपके पास:

• Duplicate entries वाला data हो और condition-based sum चाहिए हो
• Multiple columns में से dynamically एक column select करना हो
• SUMIF से ज़्यादा flexibility चाहिए हो
• Report dashboard बना रहे हों जहाँ महीना/category बदलने पर data automatically update हो

— तब यह formula आपका सबसे अच्छा साथी है।

निष्कर्ष

आज हमने देखा कि कैसे SUMPRODUCT INDEX MATCH formula का combination एक ऐसी problem को solve करता है जो अकेले SUMIF या VLOOKUP से नहीं होती। Duplicate entries हों, columns dynamic हों — यह formula सब handle कर लेता है।

सबसे पहले formula थोड़ा overwhelming लग सकता है, लेकिन जब आप इसे तीन हिस्सों में तोड़कर समझते हैं — condition array, INDEX से पूरा column लाना, और MATCH से column number ढूंढना — तो यह logic बिल्कुल clear हो जाता है।

Practice file download करें, खुद try करें, और अगर कोई सवाल हो तो नीचे comment ज़रूर करें। आपके सवाल ही हमें और tutorials बनाने की inspiration देते हैं!

अगर यह tutorial helpful लगा हो, तो इसे अपने colleagues और दोस्तों के साथ share करें। और Excel की ऐसी ही practical tricks सीखते रहने के लिए TheOfficeTutorials.com को bookmark कर लें।