3.2 SUM, SUMIF and SUMIFS
| Function | Syntax |
|---|---|
| SUM | =SUM(number1, [number2], …) |
| SUMIF | =SUMIF(range, criteria, [sum_range]) |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) |
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 |
| Amazon Now sales | =SUMIFS(G2:G11,A2:A11,"AMN*") |
537 |
| Sales 03-11 to 05-11 | =SUMIFS(G2:G11,B2:B11,">="&DATE(2026,11,3),B2:B11,"<="&DATE(2026,11,5)) |
2,031 |
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: Blinkit sales in Nashik, sales of orders taking more than 12 minutes, and sales where Category is not Fruits ("<>Fruits").