Free course · Lesson 2 of 5

Core Functions for Real Work

In this lesson you will

Write decisions with IF and IFS
Total and count with conditions using SUMIFS and COUNTIFS

Beyond SUM and AVERAGE, a handful of functions cover most real business needs: conditional logic and conditional counting/summing.

=IF(condition, value_if_true, value_if_false) — e.g. =IF(B2>=50,"Pass","Fail").
=COUNTIF(range, criteria) — counts cells matching a condition, e.g. =COUNTIF(C2:C50,"Present").
=SUMIF(range, criteria, sum_range) — adds values matching a condition, e.g. total sales for one region.
Nesting functions: functions can be combined, e.g. =IF(COUNTIF(C2:C50,"Absent")>5,"At Risk","OK").

Practical Skill: Build a class or staff attendance sheet that automatically counts total "Present" and "Absent" days per person using COUNTIF, and flags anyone with more than 5 absences using IF.

TemplateAttendance Tracker Formulas

Worksheet

=== ATTENDANCE TRACKER ===       A            B     C     D     ...   H            I1     Name         Mon   Tue   Wed         Days Present  Status2     Amuor D.     P     P     A           =COUNTIF(B2:G2,"P")   =IF(H2<10,"At Risk","OK")3     Deng K.      P     P     P           =COUNTIF(B2:G2,"P")   =IF(H2<10,"At Risk","OK") === FUNCTIONS USED ===  COUNTIF(range, "P")     Counts "Present" marks  SUMIF(range, "North", sales_range)   Totals sales for "North" region only  IF(H2<10, "At Risk", "OK")           Flags low attendance

Practice exercise

From a list of sales, calculate total sales for one branch in one month with SUMIFS, and count the sales above 10,000 SSP with COUNTIFS.

Key takeaways

IFS tests conditions in order
SUMIFS and COUNTIFS handle several conditions at once
Put criteria in cells, not inside formulas

In the full program Module 2: Lookup and Logical Functions See how this topic is taught in depth, with practical activities and an assessment.

Free self-study lesson — no certificate. For the full instructor-led program, see registration options.