Excel SUMIF, SUMIFS, COUNTIFS, AVERAGEIF(S) and MAXIFS: Profit Analysis of Delivered Orders

By Ravindra Bagale · Last updated: 10 October 2026

Practice file: blinkit-sumifs-averageifs-maxifs-practice.xlsx: the Orders sheet with every answer in column P and the formula as text in column Q, the delivered profit per city, a Read me sheet and a Your turn sheet.

Made-up data: all 24 orders, areas, products, prices and profits are made up for practice. No real customer, shop or order is used, and product names are generic.

1. The table

The orders table in A1:M25, data in rows 2 to 25. The columns used in this chapter: Product_Name (E), Status (J: Delivered, Returned or Cancelled), Delivery_Status (L: On Time or Late) and Profit (M). Profit is worked out on every row as Quantity × (Selling_Price − Cost_Price) − 20, even on orders that were cancelled or returned.

2. The wrong total: SUM

=SUM(M2:M25)

→ 327. It adds the profit of all 24 rows, including 3 cancelled and 3 returned orders. One cancelled order alone, ORD1006 (6 × Paneer), shows a profit of 100 that was never earned.

3. SUMIF: profit of delivered orders

=SUMIF(range, criteria, [sum_range])

1. range: where to check the condition (Status, J2:J25).
2. criteria: the condition ("Delivered").
3. sum_range: what to add (Profit, M2:M25).

=SUMIF(J2:J25,"Delivered",M2:M25)

→ 227, the real profit. Type the criteria as text in double quotes, exactly as it is written in the data.

The same formula with "Cancelled" gives 88 and with "Returned" 12. 227 + 88 + 12 = 327, the SUM from section 2, so nothing is missing.

4. Two conditions: SUMIFS and COUNTIFS

Paneer looks like the star product. Its profit over all rows:

=SUMIF(E2:E25,"Paneer",M2:M25)

→ 300, but that includes the cancelled ORD1006. Paneer and Delivered needs two conditions, so SUMIFS:

=SUMIFS(M2:M25,J2:J25,"Delivered",E2:E25,"Paneer")

→ 200. In SUMIFS the sum_range comes first, then pairs of criteria_range and criteria, as many pairs as you need. (In SUMIF it comes last. Section 6 has a figure of both orders.)

How many orders is that? COUNTIFS takes the same pairs, without a sum_range:

=COUNTIFS(J2:J25,"Delivered",E2:E25,"Paneer")

→ 4 delivered Paneer orders. And =COUNTIFS(J2:J25,"Delivered",L2:L25,"Late") → 6 delivered orders arrived late.

COUNTIFS works with numbers too: =COUNTIFS(J2:J25,"Delivered",M2:M25,"<0") → 6 delivered orders made a loss.

5. MAXIFS and MINIFS: best order and biggest loss

Excel has SUMIF and AVERAGEIF for one condition, but the maximum and minimum come only in the IFS form: MAXIFS and MINIFS. They work with one condition or with many.

=MAXIFS(M2:M25,J2:J25,"Delivered")

→ 80, the best delivered order (ORD1007). The plain MAX of the column would say 100, the cancelled order again.

=MINIFS(M2:M25,J2:J25,"Delivered")

→ −15, the biggest loss on a delivered order (ORD1014, one packet of snacks).

Two conditions, delivered Fruits:

=MAXIFS(M2:M25,J2:J25,"Delivered",E2:E25,"Fruits")

→ 28. Like SUMIFS, the range to look at (max_range) comes first.

Which Excel has MAXIFS and MINIFS? Excel 2019, Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. SUMIF, SUMIFS, COUNTIFS, AVERAGEIF and AVERAGEIFS work in every current version.

6. AVERAGEIF vs AVERAGEIFS

Average profit per delivered order:

=AVERAGEIF(J2:J25,"Delivered",M2:M25)

→ 12.61 (227 ÷ 18). Like SUMIF: range, criteria, and the average_range last.

Delivered and On Time is two conditions, so AVERAGEIFS, with the average_range first:

=AVERAGEIFS(M2:M25,J2:J25,"Delivered",L2:L25,"On Time")

→ 8.00 (12 orders, 96 profit). Change "On Time" to "Late" → 21.83 (6 orders, 131 profit).

So late orders earn more per order than on-time ones. Does being late make money? No: look at what the late orders are. ORD1007 (5 × Paneer) and ORD1011 (10 × Snacks) are big orders, which take longer to pack and deliver and also earn more. An average shows what is different; the rows show why.

7. Delivered profit per city

Combine what you know. Let UNIQUE write the cities in O22 (=UNIQUE(C2:C25)), then in P22, dragged down:

=SUMIFS($M$2:$M$25,$C$2:$C$25,O22,$J$2:$J$25,"Delivered")

and the number of delivered orders in Q22: =COUNTIFS($C$2:$C$25,O22,$J$2:$J$25,"Delivered").

Pune has the most delivered orders but the joint-lowest profit: small Milk, Eggs and Snacks orders where the 20 delivery cost eats the margin. Sambhaji Nagar earns the most from just 2 orders. The totals, 227 and 18, match the SUMIF and COUNTIF answers, which proves the table is complete.

8. Criteria in a cell, and a trailing space

Instead of typing "Delivered" into every formula, put it in a cell, say P30, and point to it: =SUMIF(J2:J25,P30,M2:M25). Type Returned in P30 and every answer changes.

One trap: if the criteria cell holds "Delivered " with a space at the end, SUMIF finds nothing and returns 0, without any error. Wrap the cell in TRIM, which removes extra spaces: =SUMIF(J2:J25,TRIM(O35),M2:M25) → 227 again. When a SUMIF returns 0 and you are sure there is data, look for spaces first.

9. The whole chapter on one page

1. Profit counts only delivered orders: filter with SUMIF, never plain SUM.
2. One condition: SUMIF, COUNTIF, AVERAGEIF. More: SUMIFS, COUNTIFS, AVERAGEIFS.
3. Maximum and minimum with conditions: MAXIFS and MINIFS, for one condition or many.
4. SUMIF and AVERAGEIF: the range to add or average is last. All -IFS functions: first.
5. Text criteria go in double quotes; a trailing space makes SUMIF return 0, so use TRIM.

10. Try at home

Recap

Quick revision cards

Englishमराठीहिंदी

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

More cards for every chapter: Ravi Sir's Cards.