3.2 SUM, SUMIF and SUMIFS
Quick answer: the short, self-contained version of this lesson is the guide Excel SUMIF and SUMIFS for Conditional Totals.
SUM, SUMIF and SUMIFS with example — in 30 seconds
What: SUMIFS adds only the rows that pass all your conditions.
Why: "Total sales" mixes cancelled orders with real ones. Managers want delivered sales for one city.
Tiny example: Pune has 4 orders worth ₹544, but only 3 were delivered: ₹324. =SUMIFS(G2:G11,C2:C11,"Pune",I2:I11,"Delivered")
SUM, SUMIF and SUMIFS with example — 30 सेकंदात
काय: SUMIFS फक्त तुमच्या सगळ्या conditions पूर्ण करणाऱ्या rows ची बेरीज करतो.
का: "Total sales" मध्ये cancelled orders पण मिसळतात. Manager ला एका शहराचे delivered sales हवे असतात.
छोटं उदाहरण: Pune च्या 4 orders ₹544 च्या आहेत, पण delivered फक्त 3: ₹324. =SUMIFS(G2:G11,C2:C11,"Pune",I2:I11,"Delivered")
SUM, SUMIF and SUMIFS with example — 30 सेकंड में
क्या: SUMIFS सिर्फ़ उन rows को जोड़ता है जो आपकी सारी conditions पूरी करती हैं.
क्यों: "Total sales" में cancelled orders भी मिल जाते हैं. Manager को एक शहर की delivered sales चाहिए होती है.
छोटा उदाहरण: Pune के 4 orders ₹544 के हैं, पर delivered सिर्फ़ 3: ₹324. =SUMIFS(G2:G11,C2:C11,"Pune",I2:I11,"Delivered")
First look at the data (before → after):
Expected result — Results on the ten rows
| Question | Answer (₹) |
|---|---|
| All orders (SUM) | 2,870 |
| Pune, any status (SUMIF) | 544 |
| Pune, Delivered (SUMIFS) | 324 |
About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Neha Shah) are made up for this lesson on sum sumif and sumifs.
Before table and formula as text
Before — Pune rows of the mini dataset
| A: Order ID | C: City | G: Amount | I: Status |
|---|---|---|---|
| QKT-1001 | Pune | 64 | Delivered |
| ZBK-1003 | Pune | 110 | Delivered |
| QKT-1006 | Pune | 150 | Delivered |
| QKT-1009 | Pune | 220 | Cancelled |
About this data: Demo table only — not real customer or company data. Built to practise sum sumif and sumifs.
Formula / click path
=SUM(G2:G11)
=SUMIF(C2:C11,"Pune",G2:G11)
=SUMIFS(G2:G11,C2:C11,"Pune",I2:I11,"Delivered")
Do it step by step:
- Click an empty cell, for example K2.
- Type
=SUMIFS(and select the amounts G2:G11 first. - Add the first pair: C2:C11, then
"Pune". - Add the second pair: I2:I11, then
"Delivered". Close the bracket. - Press Enter. You should see 324.
स्टेप बाय स्टेप करून बघा:
- एका रिकाम्या cell वर click करा, उदा. K2.
=SUMIFS(type करा आणि आधी amounts G2:G11 select करा.- पहिली जोडी टाका: C2:C11, मग
"Pune". - दुसरी जोडी टाका: I2:I11, मग
"Delivered". Bracket बंद करा. - Enter दाबा. उत्तर 324 दिसायला हवं.
स्टेप बाय स्टेप करके देखें:
- किसी खाली cell पर click करें, जैसे K2.
=SUMIFS(type करें और पहले amounts G2:G11 select करें.- पहली जोड़ी डालें: C2:C11, फिर
"Pune". - दूसरी जोड़ी डालें: I2:I11, फिर
"Delivered". Bracket बंद करें. - Enter दबाएँ. जवाब 324 दिखना चाहिए.
| Function | Syntax |
|---|---|
| SUM | =SUM(number1, [number2], …) |
| SUMIF | =SUMIF(range, criteria, [sum_range]) |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) |
About this data: Made-up rows so you can copy the pattern for sum sumif and sumifs. Currencies may use ₹, £, € or $.
Criteria (निकष – कोणत्या ओळी मोजायच्या याची अट) can be text ("Pune"), numbers (">15"), a cell (K1), or joined (">"&K1). Wildcards: * any characters, ? one character.
| Question | Formula | Result |
|---|---|---|
| Total sales | =SUM(G2:G11) |
2,870 |
| Pune sales | =SUMIF(C2:C11,"Pune",G2:G11) |
544 |
| Pune sales, Delivered only | =SUMIFS(G2:G11,C2:C11,"Pune",I2:I11,"Delivered") |
324 |
| Sales of Fruits | =SUMIFS(G2:G11,E2:E11,"Fruits") |
600 |
| ZipBasket sales | =SUMIFS(G2:G11,A2:A11,"ZBK*") |
537 |
| Sales 03-11 to 05-11 | =SUMIFS(G2:G11,B2:B11,">="&DATE(2026,11,3),B2:B11,"<="&DATE(2026,11,5)) |
2,031 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
Steps in Excel
- AutoSum: select the cell below the Amount column › Home › Editing › AutoSum (Alt + =) › Enter.
- Build a city summary: list the six cities in K2:K7. In L2:
=SUMIFS($G$2:$G$11,$C$2:$C$11,K2)› copy down. - Check:
=SUM(L2:L7)must equal the total 2,870.
Ravindra Bagale's Tip
The order of arguments in SUMIF and SUMIFS is reversed – in SUMIF, sum_range comes last; in SUMIFS, it comes first. Many students get confused here. I always tell them to use only SUMIFS – it works even with one condition, and you don't have to remember the order. And always check that the summary total matches the raw total.
Ravindra Bagale's Tip – मराठी
SUMIF आणि SUMIFS मध्ये arguments ची order उलटी आहे – SUMIF मध्ये sum_range शेवटी, SUMIFS मध्ये पहिला. बरेच students इथे गोंधळतात. मी नेहमी फक्त SUMIFS च वापरा असं सांगतो – एक condition असली तरी चालतं आणि order लक्षात ठेवावी लागत नाही. आणि summary चा total raw total बरोबर match करतो का ते नक्की check करा.
Ravindra Bagale's Tip – हिंदी
SUMIF और SUMIFS में arguments का order उल्टा है – SUMIF में sum_range आख़िर में, SUMIFS में सबसे पहले. बहुत से students यहाँ confuse हो जाते हैं. मैं हमेशा कहता हूँ कि सिर्फ़ SUMIFS ही इस्तेमाल करो – एक condition हो तब भी चलता है और order याद नहीं रखना पड़ता. और summary का total raw total से match करता है या नहीं, ज़रूर check करो.
Practice task
Using SUMIFS, find: QuickKart sales in Nashik, sales of orders taking more than 12 minutes, and sales where Category is not Fruits ("<>Fruits").
Loading practice tasks...
- Total of all orders (Easy): Find the total of the Amount column for all 10 orders.
- Total sales of Pune (Easy): Find the total Amount for orders where City is "Pune".
- Bank: total money moved (Easy): On the Bank sheet, find the total of the Amount (₹) column.
- Bank: total deposits (Easy): On the Bank sheet, find the total Amount (₹) where Type is "Deposit".
- Total of delivered orders (Medium): Find the total Amount of orders where Status is "Delivered".
- Big orders only (Medium): Find the total of the Amount values that are above ₹200.
- Bank: big transactions (Medium): On the Bank sheet, find the total of the Amount (₹) values that are above ₹10,000.
- Pune and Delivered (Hard): Find the total Amount for orders where City is "Pune" and Status is "Delivered".
- QuickKart delivered sales (Hard): Find the total Amount for orders where Platform is "QuickKart" and Status is "Delivered".
- Bank: Pune deposits (Hard): On the Bank sheet, find the total Amount (₹) where Branch is "Pune" and Type is "Deposit".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
