Counting data problems with a quality summary

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

Counting data problems with a quality summary

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula counts how many cells in D2:D100 are flagged with the word Check?
A =COUNTIF(D2:D100,"Check")
B =COUNT(D2:D100,"Check")
2. Which formula counts the empty cells in B2:B100 for a quality summary?
A =COUNTBLANK(B2:B100)
B =COUNT(B2:B100)
3. After cleaning, what should each problem count in the quality summary show?
A The same number as before cleaning
B 0
4. Your summary says =COUNTBLANK(C2:C100) gives 12 before cleaning and 3 after. What does this tell you?
A The data is fully clean
B 3 missing values still need to be fixed
5. Column D flags duplicates with the word Check. Which summary formula counts the duplicate rows still flagged?
A =COUNTIF(D2:D100,"Check")
B =COUNTBLANK(D2:D100)
B

Match the Pairs

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

Left columnRight column
1=COUNTIF(D2:D100,"Check") AThe word Check is missing quotation marks, so the spreadsheet treats it as a name and shows an error. It should be "Check" in quotes.
2=COUNTBLANK(B2:B100) B=COUNT(B2:B100)
3A quality summary cell that counts how many duplicate flags are left in the flag column C=COUNTIF(D2:D100,"Duplicate")
4Compare row counts before and after cleaning by counting the numbers in the cleaned column DCOUNTBLANK only takes a range and does not accept a second argument. To count cells flagged Check, use =COUNTIF(D2:D100,"Check") instead.
5=COUNTBLANK(D2:D100,"Check") ECounts how many cells in the flag column D say Check, so you know how many problem rows are left
6=COUNTIF(D2:D100,Check) FCounts how many cells in column B are empty, which shows how many missing values remain
C

Definitions

=COUNTIF(D2:D100,"Check")
D

Fill in the Blanks

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

1.
=__________(B2:B100)
2.
=_______(D2:D100,"Check")
3.
=_____(C2:C100)
4.
=COUNT(A2:A100)_COUNT(F2:F100)
5.
=IF(COUNTIF(D2:D100,"Check")=_,"OK","Fix")
E

Spot the Error

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

Question 1
A Quality summary
B Blank: =COUNT(B2:B50)
C Bad: =COUNTIF(D2:D50,"Check")
Question 2
A Before: 48 rows
B After: 45 rows
C Removed: =48+45
Question 3
A Dups: =COUNTIF(E2:E50,Dup)
B Blank: =COUNTBLANK(B2:B50)
C Bad: =COUNTIF(D2:D50,"Check")
Question 4
A Quality summary
B Blank: =COUNTBLANK(B2:B50)
C Dups: =COUNTIF(E2:E50,"Dup")
D Bad: COUNTIF(D2:D50,"Check")
Question 5
A Check count before: 7
B Cleaned the flagged rows
C Check count after: 7

Answer Key

Counting data problems with a quality summary
Section A: Quick Questions
Q1
A — =COUNTIF(D2:D100,"Check")
Q2
A — =COUNTBLANK(B2:B100)
Q3
B — 0
Q4
B — 3 missing values still need to be fixed
Q5
A — =COUNTIF(D2:D100,"Check")
Section B: Match the Pairs
Q1
=COUNTIF(D2:D100,"Check") → Counts how many cells in the flag column D say Check, so you know how many problem rows are left
Q2
=COUNTBLANK(B2:B100) → Counts how many cells in column B are empty, which shows how many missing values remain
Q3
A quality summary cell that counts how many duplicate flags are left in the flag column → =COUNTIF(D2:D100,"Duplicate")
Q4
Compare row counts before and after cleaning by counting the numbers in the cleaned column → =COUNT(B2:B100)
Q5
=COUNTBLANK(D2:D100,"Check") → COUNTBLANK only takes a range and does not accept a second argument. To count cells flagged Check, use =COUNTIF(D2:D100,"Check") instead.
Q6
=COUNTIF(D2:D100,Check) → The word Check is missing quotation marks, so the spreadsheet treats it as a name and shows an error. It should be "Check" in quotes.
Section C: Definitions
Q1
=COUNTIF(D2:D100,"Check") — A quality summary counts problems with formulas like this one. It counts cells flagged Check. COUNTBLANK counts empty cells. Each problem count should reach 0 after cleaning.
Section D: Fill in the Blanks
Q1
=COUNTBLANK(B2:B100)
Q2
=COUNTIF(D2:D100,"Check")
Q3
=COUNT(C2:C100)
Q4
=COUNT(A2:A100)-COUNT(F2:F100)
Q5
=IF(COUNTIF(D2:D100,"Check")=0,"OK","Fix")
Section E: Spot the Error
Q1
B — COUNT only counts cells that hold numbers, so it can't count missing values. Use =COUNTBLANK(B2:B50) to count the empty cells.
Q2
C — The number of rows removed is the row count before cleaning minus the row count after. It should be =48-45, which gives 3.
Q3
A — Text criteria in COUNTIF must be inside quotes. It should be =COUNTIF(E2:E50,"Dup") so it counts the cells flagged Dup.
Q4
D — A formula must start with =. Without it the cell just shows text instead of the count. It should be =COUNTIF(D2:D50,"Check").
Q5
C — After cleaning, the count of cells flagged Check should reach 0. If it still shows 7, the problems have not been fixed.