AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Count, total, decide

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.

FunctionCountsExample
COUNTcells with numbers=COUNT(C2:C9)
COUNTAcells that are not empty=COUNTA(A2:A9)
COUNTIFcells that meet a condition=COUNTIF(B2:B9,"Alto")
Worked examples
C2fx=COUNT(C2:C9)
ABCD
1SingerVoiceRehearsalsSolo?
2NodiraAlto9Yes
3BenTenor7No
4AishaAlto10No
5DiegoBass6Yes
6YukiSoprano8No
7TomBassawayNo
8AnaSopranoNo
9OmarTenor5Yes
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.
FormulaCountsResult
=COUNTIF(B2:B9,"Alto")singers with the Alto voice2
=COUNTIF(C2:C9,">=8")rehearsals 8 or more (9, 10, 8)3
=COUNTIF(D2:D9,"Yes")singers with a solo3
=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")
ABCD
1SingerVoiceRehearsalsSolo?
2NodiraAlto9Yes
3BenTenor7No
4AishaAlto10No
5DiegoBass6Yes
6YukiSoprano8No
7TomBassawayNo
8AnaSopranoNo
9OmarTenor5Yes
10Solo: No5
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).
AB
1DayMinutes late
2Mon0
3Tue4
4Wed12
5Thu0
6Fri7
7Satno bus
8Sun15
9Mon2
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?
AB
1DayMinutes late
2Mon0
3Tue4
4Wed12
5Thu0
6Fri7
7Satno bus
8Sun15
9Mon2
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?
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.