Building a frequency table with COUNTIF

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. What goes in column A of a frequency table built with COUNTIF?

A A list of each unique category, written once

B Every value from the data, repeated as many times as it appears

2. Which formula in B2 counts how many times the category in A2 appears in the data range D2:D31?

A =COUNTIF(D2:D31,A2)

B =COUNTIF(A2,D2:D31)

3. A frequency table has these categories: Red, Blue, Green, Red. Is this list set up correctly?

A Yes, every category should be listed as often as it appears

B No, Red is listed twice, so it should only appear once

4. You copy =COUNTIF($D$2:$D$31,A2) from B2 down to B3. What happens to the criteria?

A It becomes A3, so the next category is counted

B It stays as A2, so the same category is counted again

5. Your frequency table counts add up to 28, but the data has 30 entries. What is the best conclusion?

A The table is fine because totals do not need to match the data

B Something is wrong, such as a missing category or a spelling mismatch

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1=COUNTIF($B$2:$B$31,A2)		AThe arguments are in the wrong order: the range must come first and the category (criteria) second
2=SUM(B2:B5)		B=SUM(B2:B5)=COUNTA($D$2:$D$21)
3Column A lists each unique category once, and the cell beside it counts how often that category occurs		CCounts how many times the category in A2 appears in the data range B2:B31
4Check the table is complete by comparing the sum of the counts with the total number of data values		DA2: Red B2: =COUNTIF($D$2:$D$21,A2)
5=COUNTIF(D2:D21,A2) copied down without $ signs		EAdds up all the counts to check they equal the total number of data entries
6=COUNTIF(A2,$D$2:$D$21)		FThe range is relative, so it shifts down each row when copied and misses data, giving wrong counts

Definitions

=COUNTIF(range,A2)

Fill in the Blanks

Complete each line of code by filling in the missing parts.

=_______($B$2:$B$31,D2)

=COUNTIF($B$2:$B$31,__)

=COUNTIF(__________,D3)

=___(E2:E6)

=SUM(E2:E6)_COUNTA(B2:B31)

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A A2: Bus

B B2: =COUNT($D$2:$D$31,A2)

C A3: Train

D B3: =COUNTIF($D$2:$D$31,A3)

Question 2

A A2: Bus

B A3: Train

C B2: =COUNTIF($D$2:$D$31,A2)

D B3: =COUNTIF($D$2:$D$31,A2)

Question 3

A B2: =COUNTIF($D$2:$D$31,A2)

B B3: =COUNTIF($D$2:$D$31,A3)

C B4: =COUNTIF($D$2:$D$31,A4)

D B5: =SUM(B2:B3)

E C5: =IF(B5=B6,"OK","Check")

Question 4

A B2: COUNTIF($D$2:$D$31,A2)

B B3: =COUNTIF($D$2:$D$31,A3)

C B4: =COUNTIF($D$2:$D$31,A4)

Question 5

A B2: =COUNTIF($D$2:$D$31,A2)

B B3: =COUNTIF($D$2:$D$31,A3)

C B4: =COUNTIF($D$4:$D$31,A4)

Answer Key

Building a frequency table with COUNTIF

Section A: Quick Questions

A — A list of each unique category, written once

A — =COUNTIF(D2:D31,A2)

B — No, Red is listed twice, so it should only appear once

A — It becomes A3, so the next category is counted

B — Something is wrong, such as a missing category or a spelling mismatch

Section B: Match the Pairs

=COUNTIF($B$2:$B$31,A2) → Counts how many times the category in A2 appears in the data range B2:B31

=SUM(B2:B5) → Adds up all the counts to check they equal the total number of data entries

Column A lists each unique category once, and the cell beside it counts how often that category occurs → A2: Red B2: =COUNTIF($D$2:$D$21,A2)

Check the table is complete by comparing the sum of the counts with the total number of data values → =SUM(B2:B5)=COUNTA($D$2:$D$21)

=COUNTIF(D2:D21,A2) copied down without $ signs → The range is relative, so it shifts down each row when copied and misses data, giving wrong counts

=COUNTIF(A2,$D$2:$D$21) → The arguments are in the wrong order: the range must come first and the category (criteria) second

Section C: Definitions

=COUNTIF(range,A2) — Used in a frequency table: list each category in column A, then put this beside it to count how many times that category appears in the data range. Copy it down for every category.

Section D: Fill in the Blanks

=COUNTIF($B$2:$B$31,D2)

=COUNTIF($B$2:$B$31,D3)

=SUM(E2:E6)

=SUM(E2:E6)=COUNTA(B2:B31)

Section E: Spot the Error

B — COUNT only counts numbers and has no criteria argument. A frequency table needs =COUNTIF($D$2:$D$31,A2) to count how many times the category in A2 appears.

D — The formula beside Train must use A3 as its criteria. Using A2 counts Bus again, so Train's count is wrong. It should be =COUNTIF($D$2:$D$31,A3).

D — The total must add every count in the table. B2:B3 leaves out B4, so the check against the data total would fail. It should be =SUM(B2:B4).

A — A formula must start with =. Without it the spreadsheet treats the entry as text. It should be =COUNTIF($D$2:$D$31,A2).

C — The data range must be the same in every formula and cover all the data. $D$4 skips rows 2 and 3, so some items are missed. It should be $D$2:$D$31.