Purpose
By the end of this lesson, you will be able to write and apply SUM, AVERAGE, and IF formulas to real business calculations.
Lesson Explanation
SUM adds up a range of numbers: =SUM(B2:B10) adds every value from B2 through B10. AVERAGE calculates the mean: =AVERAGE(B2:B10) returns the average of that same range.
IF tests a condition and returns one value if true, another if false: =IF(B2>100,”Over Budget”,”On Budget”) checks whether B2 is greater than 100, returning the text “Over Budget” if true and “On Budget” if false.
These functions can be combined. =SUM(IF(C2:C10=”Approved”,B2:B10,0)) (entered as an array formula, or more simply using SUMIF, covered next) sums only the values where a condition is met.
SUMIF and COUNTIF are the more direct tools for conditional totals: =SUMIF(C2:C10,”Approved”,B2:B10) sums column B only where the matching cell in column C equals “Approved.” =COUNTIF(C2:C10,”Approved”) counts how many cells in that range equal “Approved,” without needing a second range at all.
Practice Questions
1. Write a formula that sums all values in the range D2:D20.
View Answer
=SUM(D2:D20)2. Write a formula that calculates the average of the range E2:E15.
View Answer
=AVERAGE(E2:E15)3. Write an IF formula for cell F2 that returns “Pass” if the value in E2 is 70 or greater, and “Fail” otherwise.
View Answer
=IF(E2>=70,”Pass”,”Fail”)4. A column of expense amounts is in B2:B30, and a column of matching categories (“Travel,” “Supplies,” “Other”) is in C2:C30. Write a formula that sums only the “Travel” expenses.
View Answer
=SUMIF(C2:C30,”Travel”,B2:B30)5. Using the same data, write a formula that counts how many expenses are categorized as “Supplies.”
View Answer
=COUNTIF(C2:C30,”Supplies”)6. A manager wants a formula that labels each row “Bonus Eligible” if the sales value in D2 exceeds 50000, and “Not Eligible” otherwise. Write this formula.
View Answer
=IF(D2>50000,”Bonus Eligible”,”Not Eligible”)7. A SUM formula returns 0 even though the range clearly contains numbers. What is one likely cause?
View Answer
The values in the range may actually be stored as text rather than true numbers (a common issue when data is pasted or imported from another system), which SUM ignores.
8. Write a formula that averages only the values in B2:B20 where the corresponding cell in column C equals “North” (hint: this requires a conditional average function, not plain AVERAGE).
View Answer
=AVERAGEIF(C2:C20,”North”,B2:B20)9. A formula =IF(A2=”Yes”,1,0) is applied to a column, and then =SUM is used on the results. What does this combination effectively calculate?
View Answer
A count of how many cells in that column contain “Yes” – the IF converts each match to a 1 (and each non-match to 0), and SUM adds up the 1s.
10. Why might COUNTIF be a more direct tool than combining IF and SUM for simply counting matching entries?
View Answer
COUNTIF performs the count directly in one step, without needing to first convert each row to a 1/0 with IF and then total those with SUM.
11. A dataset has a “Region” column and a “Revenue” column. Write a formula to sum revenue specifically for the region “West.”
View Answer
=SUMIF(Region_column,”West”,Revenue_column) – e.g., if Region is column C and Revenue is column D: =SUMIF(C2:C50,”West”,D2:D50).
12. A finance team wants a single formula that returns “Review Needed” if a variance value in G2 is either above 10% or below -10%, and “OK” otherwise. Write this using nested logic.