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 के लिए मैंने एक छोटा टेबल बनाया है जो इस प्रकार दिखाता है👇

Cell	Content	Purpose
B2	Product	Label
C2	Marker (या जो चाहें)	✏️ यहाँ product name डालेंगे — यह cell formula में use होगी
B3	Month	Label
C3	Jan (या Feb / Mar)	✏️ यहाँ month name डालेंगे — formula इसी से column खोजेगा
B4	Total Sales Value	Label
D4	Formula यहाँ आएगा	🎯 यहाँ SUMPRODUCT formula लगेगा

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

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

Column B	Column C	Column D	Column E
Product	Jan	Feb	Mar
Pen	2500	3000	5000
Notebook	4000	4500	6000
Pencil	2200	2800	3200
Eraser	2100	2600	3100
Marker	3500	3900	4200
Pen	2700	3200	5200
Notebook	4200	4800	6200
Pencil	2400	2900	3400
… इसी तरह Marker तक 4 बार repeat होगा (Row 8 से Row 27 तक कुल 20 rows)

ध्यान दें: 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 हैं:

Row	Product	Jan Sales
B12:C12	Marker	3,500
B17:C17	Marker	3,600
B22:C22	Marker	3,800
B27:C27	Marker	4,000
Manual Total		14,900

और हमारा 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 बदलकर देखें:

C2 (Product)	C3 (Month)	Expected Total (Formula Result)
Pen	Jan	2500+2700+2900+3000 = 11,100
Notebook	Feb	4500+4800+5000+5200 = 19,500
Pencil	Mar	3200+3400+3600+3800 = 14,000
Eraser	Jan	2100+2300+2500+2600 = 9,500
Marker	Jan	3500+3600+3800+4000 = 14,900 ✅

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

तीनों Functions का Quick Reference

Function	Syntax	इस formula में क्या काम किया?
SUMPRODUCT	=SUMPRODUCT(array1, array2...)	दो arrays को multiply करके उनका sum निकाला — condition-based sum के लिए perfect
INDEX	=INDEX(array, row, col)	Row=0 देने पर पूरा column return किया — सिर्फ एक value नहीं, पूरी range
MATCH	=MATCH(value, range, type)	Month name देखकर automatically उसका column number ढूंढा — dynamic column selection

🌟 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 कर लें।