Ravindra BagaleCourses & study guides मराठी Track your progress

3. Formulas and Functions

Practice and Answer Key: SUMIFS, COUNTIFS and IF

By Last updated:

Goal of this practice: Answer five manager questions on eight rows with SUMIFS, COUNTIFS, AVERAGEIF and IF.

Suppose Ruhi Bagale manages Kolhapur and Solapur. She sends you this sheet (A1:F9) and asks five questions before the Monday call. All data is fictional practice data.

A: Order ID B: City C: Platform D: Amount (₹) E: Mins F: Status
AMN-2001 Kolhapur Amazon Now 340 11 Delivered
BLK-2002 Solapur Blinkit 180 17 Delivered
BLK-2003 Kolhapur Blinkit 520 9 Delivered
AMN-2004 Solapur Amazon Now 260 19 Cancelled
BLK-2005 Kolhapur Blinkit 150 16 Delivered
AMN-2006 Solapur Amazon Now 610 12 Delivered
BLK-2007 Kolhapur Blinkit 90 14 Returned
BLK-2008 Solapur Blinkit 430 8 Delivered

Exercise (one under the other):

  1. Type the eight rows in A1:F9 with the headers in row 1.
  2. In H2, find delivered sales for Kolhapur with SUMIFS.
  3. In H3, find delivered sales for Solapur.
  4. In H4, count delivered orders in Solapur with COUNTIFS.
  5. In H5, count orders that took more than 15 minutes.
  6. In H6, find the average minutes for Kolhapur with AVERAGEIF.
  7. In G2, write an IF that shows "Late" when Mins is more than 15, else "On time". Copy it down to G9.

Expected result (check your numbers):

Cell Question Answer
H2 Delivered sales, Kolhapur (₹) 1010
H3 Delivered sales, Solapur (₹) 1220
H4 Delivered orders, Solapur 3
H5 Orders over 15 minutes 3
H6 Average minutes, Kolhapur 12.5
G2:G9 Rows tagged "Late" BLK-2002, AMN-2004, BLK-2005

Check: Kolhapur 340 + 520 + 150 = 1010. BLK-2007 is Returned, so it is not counted. Solapur 180 + 610 + 430 = 1220; AMN-2004 is Cancelled.

Answer key (open after you try) 
H2: =SUMIFS(D2:D9,B2:B9,"Kolhapur",F2:F9,"Delivered")
H3: =SUMIFS(D2:D9,B2:B9,"Solapur",F2:F9,"Delivered")
H4: =COUNTIFS(B2:B9,"Solapur",F2:F9,"Delivered")
H5: =COUNTIF(E2:E9,">15")
H6: =AVERAGEIF(B2:B9,"Kolhapur",E2:E9)
G2: =IF(E2>15,"Late","On time")

In SUMIFS the sum range comes first. In AVERAGEIF it comes last. That is the most common slip in this practice.

Common mistakes:

What you see Why Fix
H2 shows 1100 The Status condition is missing, so the Returned order is added Add the pair F2:F9,"Delivered"
H5 shows 0 The condition was typed as >15 without quotes Write ">15" inside double quotes
#VALUE! in H2 Ranges of different sizes, e.g. D2:D9 with B2:B10 Make every range cover rows 2 to 9
G column says "Late" for 15 minutes >= was used instead of > Use E2>15 exactly as the question says

Ravindra Bagale's rule: read the question twice, write the conditions on paper, then type the formula.

Thodkyaat sangaycha tar (quick recap)

SUMIFS/COUNTIFS/AVERAGEIFS ne conditional totals, text functions ne data todne-jodne (TEXT ne format), dates ha number aahe he lakshat theva, IF/IFS/SWITCH ne logic, IFNA ne lookup errors, SUMPRODUCT ne advanced conditions – aani error aala tar tyacha arth vachun trace kara. Aata pudhe jaauya – lookups!

Loading practice tasks...

  1. Kolhapur delivered sales (Easy): Find the total Amount for Kolhapur orders that are Delivered.
  2. Solapur delivered sales (Easy): Find the total Amount for Solapur orders that are Delivered.
  3. Institute: fees received (Easy): Find the total Fee where Paid (Fees!E2:E6) is "Yes".
  4. Solapur delivered count (Medium): Count Solapur orders that are Delivered.
  5. Late orders (Medium): Count orders with Mins (E) more than 15.
  6. Institute: unpaid in Pune (Medium): Count Pune students who have not paid.
  7. Institute: Excel fees paid (Medium): Find the total paid fees of the "Excel" course.
  8. Kolhapur average time (Hard): Find the average Mins for Kolhapur.
  9. Late tag (Hard): For row 3, show "Late" if E3 is more than 15, else "On time".
  10. Institute: reminder tag (Hard): For row 6, show "Send reminder" if Fees!E6 is "No", else "Paid".

See all practice tasks in the Task Hub →