Spreadsheets at Depth
Counting and adding with a condition
Work out what COUNTIF and SUMIF show for a sheet you are given, and read the test each of them is applying.
A SUM over a range adds every cell in it, and a COUNT counts every number in it. That is exactly what you want until the day the question changes from "what did we sell?" to "what did we sell of the ones that made a profit?" — and then a sheet that can only add whole columns stops being useful. COUNTIF and SUMIF are the same two jobs done to only some of the cells. Each of them takes a range and a test, with a comma between them, and goes down the range asking that one test of each cell in turn. COUNTIF reports how many cells passed. SUMIF reports what those cells add up to.
The test goes in quotation marks, and every cell is asked it on its own
A test is written as a piece of writing — ">20", "<100", "pen" — and the quotation marks are part of how it is written, not part of what it means. What matters is that the test is asked of each cell separately: there is never a comparison between one cell and the next, and nothing about the size of the range changes what any one cell does. The comparisons behave the way they always have, which is worth saying because it is where these formulas go wrong: > means strictly more, so a cell holding exactly the number in the test does not pass it, and >= is the one that lets that cell through.
Worked example
One range, two functions with almost the same name. What does each of these show?
=COUNT(P1:P5) =COUNTIF(P1:P5, ">7")
The sheet holds P1 = 9, P2 = 2, P3 = 11, P4 = 7 and P5 = 4. The first formula counts the numbers in the range and pays no attention to what any of them is, so it shows 5.
This is the function from last year, and it is worth having in mind as the thing the new one is a narrowing of.
Try it together
Now build the formula rather than read one. A sheet holds twenty pupils' scores in R1:R20, and you want to know how many of them scored more than 60.
Two decisions have to be made in order: which function answers this question at all, and what its test has to say.
1.Which of the two functions in this skill answers "how many" — write its name.
Have a go
Have a go on your own. A sheet holds S1 = 3, S2 = 12, S3 = 12 and S4 = 7. What number does this formula show?
=COUNTIF(S1:S4, "<10")
Ready to practise?
Eight questions on what you have just read. Nothing is timed, and you can play as many times as you like.