- Practice
- About 13 min
- 5 practice questions
Trace questions: IF, COUNTIF, SUMIF and ROUND on a marks list
Trace IF, COUNTIF, SUMIF, AVERAGE and ROUND formulas on one list of marks, keeping a tally so that no cell is skipped.
If something looks wrong, please tell us.
Trace questions give one list of data and several formulas, one after another. The marks below are illustrative and will be used in every example, so you can see how one table feeds many questions. Keep a tally mark beside each value as you check it against a condition.
The data
| Cell | B2 | B3 | B4 | B5 | B6 | B7 | B8 | B9 |
|---|---|---|---|---|---|---|---|---|
| Marks | 35 | 52 | 78 | 40 | 39 | 90 | 64 | 47 |
The functions
- =COUNTIF(range, condition) counts the cells that meet the condition.
- =SUMIF(range, condition) adds the cells that meet it.
- =IF(test, a, b) returns a when the test is TRUE and b otherwise.
- =ROUND(number, digits) rounds to the given number of decimal places; a negative number of digits rounds to tens, hundreds and so on.
A condition such as ">=40" is put in quotation marks with the operator inside. The condition applies to each cell of the range one by one.
Worked examples
Example 1. =COUNTIF(B2:B9,">=40") counts the marks 52, 78, 40, 90, 64 and 47. The value 40 qualifies because the test is at least 40. The result is 6.
Example 2. =COUNTIF(B2:B9,"<40") counts 35 and 39, so the answer is 2. The two counts (6 and 2) add up to 8, the number of marks, which is a useful check.
Example 3. =SUMIF(B2:B9,">50") adds the marks greater than 50: 52 + 78 + 90 + 64 = 284. The mark 40 is not included, and neither is 47.
Example 4. The total of all marks is 35 + 52 + 78 + 40 + 39 + 90 + 64 + 47 = 445, so the average is 445 / 8 = 55.625. Then =ROUND(AVERAGE(B2:B9),1) gives 55.6, =ROUND(AVERAGE(B2:B9),0) gives 56 and =ROUND(AVERAGE(B2:B9),-1) gives 60.
Example 5. =MAX(B2:B9)-MIN(B2:B9) = 90 - 35 = 55. This is the range of the marks.
Example 6. In C2 the formula =IF(B2>=40,"P","F") is copied down to C9. C2 tests 35, which is below 40, so it shows F. The column then reads F, P, P, P, F, P, P, P. Counting the letter P with =COUNTIF(C2:C9,"P") gives 6, which agrees with Example 1.
Example 7. =AVERAGEIF(B2:B9,">=40") averages only the six qualifying marks. Their total is 52 + 78 + 40 + 90 + 64 + 47 = 371, so the result is 371 / 6 = 61.83, and =ROUND(AVERAGEIF(B2:B9,">=40"),1) gives 61.8.
Example 8. A cell E1 holds the pass mark 50. A condition can join the operator to the cell with the ampersand: =COUNTIF(B2:B9,">"&E1) counts the marks above 50, which are 52, 78, 90 and 64, so the answer is 4. Changing E1 changes the count without editing the formula.
Tally every cell against the condition, and check that the parts add up to the whole.
Checks that catch errors
- The count meeting a condition plus the count not meeting it must equal the total count.
- Average times the number of items must equal the total.
- A rounded value must lie within half a unit of the original: 55.6 is within 0.05 of 55.625.
Key takeaways. Test each cell against the condition one by one. At least 40 includes 40 itself, while more than 50 excludes 50. ROUND with a negative number rounds to tens or hundreds.
Check your understanding
-
1. With the marks above, what does =COUNTIF(B2:B9,">=40") return?
- 5
- 8
- 6
- 7
Show answer
Answer: C. The marks 52, 78, 40, 90, 64 and 47 meet the test, so the count is 6. The mark 40 is included.
-
2. What does =SUMIF(B2:B9,">50") return?
- 284
- 244
- 324
- 445
Show answer
Answer: A. 52 + 78 + 90 + 64 = 284. The marks 40 and 47 are not above 50.
-
3. What does =ROUND(445/8,1) return?
- 55.6
- 55.7
- 56
- 55.62
Show answer
Answer: A. 445 / 8 = 55.625, which rounds to 55.6 at one decimal place.
-
4. =MAX(B2:B9)-MIN(B2:B9) returns:
- 35
- 90
- 45
- 55
Show answer
Answer: D. The largest mark is 90 and the smallest is 35, so 90 - 35 = 55.
-
5. =IF(B2>=40,"P","F") is evaluated for B2 = 35. The result is:
- P
- F
- 35
- TRUE
Show answer
Answer: B. 35 >= 40 is FALSE, so the third part, F, is returned.
Sign in to keep track of the lessons you finish.