समझिए
1. अभ्यास शीट
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Salesperson | Region | Product | Amount |
| 2 | 01-Apr | Amit | North | Laptop | 55000 |
| 3 | 03-Apr | Neha | South | Mobile | 18000 |
| 4 | 05-Apr | Amit | North | Mobile | 22000 |
| 5 | 08-Apr | Ravi | West | Laptop | 61000 |
| 6 | 12-Apr | Neha | South | Laptop | 48000 |
| 7 | 15-Apr | Amit | South | Tablet | 30000 |
| 8 | 20-Apr | Ravi | North | Mobile | 15000 |
2. SUMIFS — शर्त के साथ योग
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
पहले क्या जोड़ना है, फिर जोड़ियों में "कहाँ देखना है, क्या ढूँढना है"।
Amit की कुल बिक्री:
=SUMIFS(E2:E8, B2:B8, "Amit") → 107000
Amit की North रीजन की बिक्री (2 शर्तें):
=SUMIFS(E2:E8, B2:B8, "Amit", C2:C8, "North") → 77000
कई शर्तें AND की तरह काम करती हैं — सब मैच होनी चाहिए।
3. COUNTIFS — शर्त के साथ गिनती
इसमें sum_range नहीं होता, सीधे जोड़ियाँ:
=COUNTIFS(D2:D8, "Laptop") → 3
=COUNTIFS(D2:D8, "Laptop", E2:E8, ">50000") → 2
4. AVERAGEIFS — शर्त के साथ औसत
संरचना SUMIFS जैसी:
=AVERAGEIFS(E2:E8, D2:D8, "Mobile") → 18333.33
कोई रो मैच न हो तो #DIV/0! आता है (लेसन 7 में इसे संभालना सीखेंगे)।
5. शर्त (criteria) लिखने के तरीके
| चाहिए | Criteria | नोट |
|---|---|---|
| बिल्कुल यही टेक्स्ट | "Amit" |
केस-इनसेंसिटिव |
| किसी नंबर से बड़ा | ">50000" |
ऑपरेटर कोट्स के अंदर |
| बराबर नहीं | "<>North" |
|
| "Lap" से शुरू | "Lap*" |
* = कितने भी अक्षर |
| खाली सेल | "" |
|
| सेल से वैल्यू | H1 |
बिना कोट्स |
| सेल + ऑपरेटर | ">"&H1 |
& से जोड़ें |
आख़िरी वाला सबसे ज़रूरी है। ">H1" लिखेंगे तो Excel सचमुच "H1" टेक्स्ट ढूँढेगा। सही है: ">"&H1।
6. तारीख़ की रेंज — किसी अवधि की बिक्री
H1 में 01-Apr और H2 में 10-Apr:
=SUMIFS(E2:E8, A2:A8, ">="&H1, A2:A8, "<="&H2) → 156000
एक ही कॉलम (A) पर दो शर्तें — बिल्कुल सही है।
7. सारांश रिपोर्ट बनाएँ ($ के साथ)
G2:G4 में North, South, West लिखें। H2 में:
=SUMIFS($E$2:$E$8, $C$2:$C$8, G2)
नीचे खींचें। रेंज लॉक हैं, सिर्फ़ G2 बदलेगा। लेसन 1 का $ यहाँ काम आया।
8. SUMIF बनाम SUMIFS
पुराने SUMIF (बिना S) में आर्ग्युमेंट का क्रम अलग है — sum_range आख़िर में आता है। उलझन से बचने के लिए हमेशा SUMIFS इस्तेमाल करें, एक शर्त के लिए भी।
आम गलतियाँ
रेंज का साइज़ अलग होना (E2:E8 और B2:B10) — #VALUE! एरर। ऑपरेटर कोट्स के बाहर लिखना या सेल रेफ़रेंस कोट्स के अंदर लिखना। Amount कॉलम में टेक्स्ट वाले नंबर, जो योग में नहीं जुड़ते।