Counting by hand does not scale
Filter + row count works with five answers. With fifty respondents and ten flavours it does not. A summary table (Pivot table) or the function COUNTIF / POČET.KDYŽ summarises data in a few clicks.
Today COUNTIF and a handmade summary table D:E — pivot as a bonus in Sheets (Insert → Pivot table).
COUNTIF on the survey
-
Sheet
Pruzkum. Column A raw answers (22 rows). In D1:E1 headingsAnswer,Count. -
D2
roll, D3apple, D4bar, D5nothing. E2:=POČET.KDYŽ(A:A;D2)or in English=COUNTIF(A:A,D2) -
复制 E2 to E5. E2 shows how many times
rollis in A. Watch out: A:A counts the whole column including empties — betterA2:A100. -
=SUMA(E2:E5)must be 22. If not, the data is not cleaned or range A is wrong. -
Sheets bonus: select A1:B22 → Insert → Pivot table. Rows = Answer, Values = Count (COUNTA). The same result as D:E, faster when data changes.
The summary table D:E is the source for the chart from Chart maker. When an answer is added in A, COUNTIF recalculates — the chart must take D1:E5, not an old handmade number.
Pivot vs. COUNTIF
| COUNTIF | Pivot | |
|---|---|---|
| When | A few categories, a fixed list | Lots of rows, answers change |
| Where | Next to it on the same sheet | A new sheet or next to it |
| Chart | D:E directly | 复制 from the pivot or link |
开始 with COUNTIF — you see the formula. Pivot only when A has hundreds of rows.
What often goes wrong
COUNTIF(A:A;D2) with a heading in A1. Sometimes +1. Use A2:A500.
Spaces in the text. apple ≠ apple. Count 0. Clean the text before COUNTIF.
Forgotten SUM check. Total of counts ≠ number of respondents = a mistake.
🏫 Grade average — COUNTIF elsewhere
=POČET.KDYŽ(B2:B6;">2") — how many subjects worse than 2? A helper number, not a replacement for the average. Average = PRŮMĚR(B2:B6).
💰 Pocket money — days in minus
=POČET.KDYŽ(D2:D8;"<0") — how many days is Left negative? (Syntax can be POČET.KDYŽ(D2:D8;"<0") in CS.) Compare with the filter on column E.
📊 Class survey — the core
D:E with COUNTIF = automatic summary for the chart. You add a row in A → E updates (when the range is A2:A100).
🏆 Mini dashboard — link to the summary
The dashboard does not show 22 rows. It shows the survey winner from cell D2 or =INDEX(D2:D5;KDYŽ(E2:E5=MAX(E2:E5);0;1)) — later. For now a sentence next to the chart is enough.
Now you 💪
First: D:E with COUNTIF, SUM = 22.
Second: （可选） pivot from column A — compare with E2:E5.
Check: Counts match a handmade filter. Chart from D:E works. SUM fits.
What to take from this lesson
- POČET.KDYŽ / COUNTIF = how many times a value is in a range.
- Summary table D:E = input for the survey chart.
- Always SUM of counts = number of answers.
© 2026 Ing. Martin Polak / AlgoRhino · 内容使用条款