Week 16 · 2.1 Spreadsheets and analysing data · 3 lessons
2
Counting cells
Computing 9: Topic 2.1, pp. 168–197
GoalCount the numbers, the filled cells, and the cells that meet a condition, with COUNT, COUNTA and COUNTIF.
New words
COUNT · a function that counts the cells in a range that hold numbersCOUNTA · a function that counts the cells in a range that are not empty, whether they hold numbers or textCOUNTIF · a function that counts only the cells in a range that meet a conditioncriteria · the condition that a cell must meet to be counted, such as "Alto" or ">=8"
Explanation
There are three counting functions. COUNT counts only the cells that hold numbers; text and empty cells are skipped. COUNTA counts every cell that is not empty, so it includes text but not blanks. COUNTIF has two parameters, the range and the criteria. It counts only the cells that match. A word is written in double quotation marks, for example "Alto". A condition with a comparison sign is also written in quotation marks, for example ">=8". An exact number can be written without them. COUNTIF with a number test ignores text cells, so it does not count the word away as a small number. To count the cells that do not match, subtract the matching count from the total.
Function
Counts
Example
COUNT
cells with numbers
=COUNT(C2:C9)
COUNTA
cells that are not empty
=COUNTA(A2:A9)
COUNTIF
cells that meet a condition
=COUNTIF(B2:B9,"Alto")
Worked examples
C2fx=COUNT(C2:C9)
A
B
C
D
1
Singer
Voice
Rehearsals
Solo?
2
Nodira
Alto
9
Yes
3
Ben
Tenor
7
No
4
Aisha
Alto
10
No
5
Diego
Bass
6
Yes
6
Yuki
Soprano
8
No
7
Tom
Bass
away
No
8
Ana
Soprano
No
9
Omar
Tenor
5
Yes
COUNT and COUNTA on a choir list. Column C holds the number of rehearsals attended out of 10. Tom’s cell holds the word away and Ana’s cell is empty. =COUNT(C2:C9) counts the six numbers and gives 6. =COUNTA(C2:C9) counts the six numbers and the word away, but not the empty cell, and gives 7. =COUNTA(A2:A9) gives 8, because all the names are there.
Formula
Counts
Result
=COUNTIF(B2:B9,"Alto")
singers with the Alto voice
2
=COUNTIF(C2:C9,">=8")
rehearsals 8 or more (9, 10, 8)
3
=COUNTIF(D2:D9,"Yes")
singers with a solo
3
=COUNTIF(C2:C9,"<7")
rehearsals less than 7 (6, 5)
2
COUNTIF with different criteria. The first parameter is the range to look in and the second parameter is the condition. The word criteria in quotation marks counts text matches. The comparison ">=8" counts the numbers of 8 or more. The word away is ignored by the number test.
D10fx=COUNTIF(D2:D9,"No")
A
B
C
D
1
Singer
Voice
Rehearsals
Solo?
2
Nodira
Alto
9
Yes
3
Ben
Tenor
7
No
4
Aisha
Alto
10
No
5
Diego
Bass
6
Yes
6
Yuki
Soprano
8
No
7
Tom
Bass
away
No
8
Ana
Soprano
No
9
Omar
Tenor
5
Yes
10
Solo: No
5
Counting what does not match. How many singers have no solo? Either =COUNTIF(D2:D9,"No") gives 5 directly, or the total number of singers minus those with a solo gives the same answer: =COUNTA(A2:A9)-COUNTIF(D2:D9,"Yes") is 8 − 3 = 5. Two different formulas that give the same answer are a useful check.
Class activity
Count by hand (no computer). Write ten rows of data on paper about your class, for example a favourite fruit and the number of siblings; leave two cells empty and write the word none in one numeric cell. Ask a partner to work out the answers to: COUNT of the numbers, COUNTA of the numbers, COUNTIF for one fruit, and COUNTIF for ‘more than 2’. Compare the numbers they get and explain any difference between COUNT and COUNTA.
Practice
1
A bus is late by a number of minutes on eight days (B2:B9). On one day the bus did not come and the cell says no bus. Work out =COUNT(B2:B9) and =COUNTA(B2:B9).
A
B
1
Day
Minutes late
2
Mon
0
3
Tue
4
4
Wed
12
5
Thu
0
6
Fri
7
7
Sat
no bus
8
Sun
15
9
Mon
2
COUNT counts the seven numbers, so it gives 7. COUNTA counts all eight cells, because none is empty, so it gives 8. The cell no bus is text, so COUNT skips it.
2
Write the formula for each question, and work out the answer from the table. (a) On how many days was the bus on time (0 minutes late)? (b) On how many days was it more than 5 minutes late? (c) On how many days was it at least 10 minutes late?
A
B
1
Day
Minutes late
2
Mon
0
3
Tue
4
4
Wed
12
5
Thu
0
6
Fri
7
7
Sat
no bus
8
Sun
15
9
Mon
2
(a) =COUNTIF(B2:B9,0) gives 2. (b) =COUNTIF(B2:B9,">5") counts 12, 7 and 15, so 3. (c) =COUNTIF(B2:B9,">=10") counts 12 and 15, so 2.
3
Which function would you use for each job: COUNT, COUNTA or COUNTIF? (a) How many pupils are on a list of names? (b) How many test marks (numbers) have been entered? (c) How many pupils scored more than 40?
(a) COUNTA, because names are text. (b) COUNT, because it counts numbers. (c) COUNTIF, with the criteria ">40".
4
A learner wrote =COUNT(A2:A9) for the names in column A and got 0. Another wrote =COUNTIF(B2:B9,>5). Explain the problem in each.
COUNT counts numbers only, and names are text, so the answer is 0; use COUNTA. In the second formula the criteria need quotation marks: =COUNTIF(B2:B9,">5").