Zum Inhalt springen
GridBelt
  • Excel-Test
  • Übungen
  • Programme
  • Preise
Anmelden
Kostenlosen Test starten
  • Excel-Test
  • Übungen
  • Programme
  • Preise
AnmeldenKonto erstellen
Kostenlosen Test starten
GridBelt

Excel üben – direkt im Browser.

Excel-Test

  • Stufe 1: Anfänger
  • Stufe 2: Fortgeschrittene
  • Stufe 3: Experten
  • Excel-Test in 7 Tagen
  • Bestenliste
  • Excel-Test im Bewerbungsverfahren
  • Excel-Test von Indeed

Übungen

  • Alle Übungen
  • Programme
  • Für Anfänger

Ratgeber

  • Excel-Formeln
  • Excel-Tastenkombinationen

Unternehmen

  • Über uns
  • Preise
  • So bewerten wir

© GridBelt. Alle Rechte vorbehalten.

  • Datenschutzerklärung
  • Nutzungsbedingungen
  • Rückerstattungsrichtlinie

Excel ist eine Marke der Microsoft Corporation. Indeed, TestGorilla, SHL und andere Marken sind Eigentum ihrer jeweiligen Inhaber. GridBelt ist eine unabhängige Übungsplattform, steht mit Microsoft, TestGorilla, SHL oder Indeed in keiner Verbindung und wird von keinem dieser Unternehmen unterstützt oder gesponsert.

  1. Übungen
  2. /
  3. Pivot tables

Pivot table practice: Count of Amount instead of Sum, find and fix the cause

  • Fortgeschrittene
  • ~12 Min.
  • Pivot tables

Übungstext auf Englisch

An assistant in the finance team built a PivotTable from the 150 October expense claims on the Claims sheet to show how much each department claimed. What she got is pictured below: her PivotTable Fields pane and a report with a single number labelled Count of Amount. Before the PivotTable is rebuilt, you check the Amount column with formulas, then answer four questions about fixing the report.

Left, the Pivot sheet: A1 Department with a filter drop-down showing (All), A3 Count of Amount, A4 142. Right, the PivotTable Fields pane: fields Claim ID, Date, Employee, Department (checked), Category, Amount (checked); Filters: Department; Columns: empty; Rows: empty; Values: Count of Amount, outlined in green.
The PivotTable as the assistant left it: the report (left) and its Fields pane (right).

Ihre Aufgabe

  1. 1Look at column F first: real numbers sit at the right edge of their cells. F2 and a number of other amounts sit at the left, the way text does, and a few claims have no amount at all.
  2. 2Click G2 and type =IF(F2="",0,VALUE(F2)): an empty amount becomes 0, VALUE turns text such as 233.54 into the number 233.54, and real numbers pass through unchanged. Fill G2 down to G151: select G2:G151 and press Ctrl+D (Windows) / ⌘D (Mac) — safer here than a double-click on the fill handle, which can stop early at the empty amounts in column F (the first is F6).
  3. 3Click J2 and count the cells of F2:F151 that hold text: =SUMPRODUCT(--ISTEXT(F2:F151)). ISTEXT returns TRUE for every text cell, the two minus signs turn TRUE/FALSE into 1/0, and SUMPRODUCT adds them up.
  4. 4Click J3 and total the cleaned column: =SUM(G2:G151). That is the number a Sum of Amount should report once column F holds real numbers.
  5. 5Then answer the four questions about the PivotTable and click Check.

Antwortzellen

Fragen

Wählen Sie hier Ihre Antworten. „Prüfen“ bewertet sie zusammen mit den Antwortzellen.

  1. Frage 1Which of these make Excel summarize Amount as Count instead of Sum when the field is dropped into Values? Select all that apply.

    Wählen Sie alle zutreffenden Antworten.

  2. Frage 2Suppose you change nothing in the data and only pick Sum in Value Field Settings, as pictured. What will Sum of Amount show?

    Wählen Sie eine Antwort.

    Value Field Settings dialog for the Amount field: Source Name Amount, Custom Name Sum of Amount, tab Summarize Values By, list Sum (selected), Count, Average, Max, Min, Product; buttons Number Format, OK and Cancel.
  3. Frage 3A week later, 12 new claims are pasted below the list, in rows 152 to 163 (pictured). After Refresh the report still leaves them out. Which of these make the report include them? Select all that apply.

    Wählen Sie alle zutreffenden Antworten.

    The Claims sheet scrolled down under its frozen header row (Claim ID, Date, Employee, Department, Category, Amount). Rows 150 to 151 hold the last October claims, EXP-1149 to EXP-1150; rows 152 to 163 hold new claims EXP-1151 to EXP-1162, dated 2026-11-02 to 2026-11-06. A dashed outline marks A1:F151. Below, the Change PivotTable Data Source dialog: Select a table or range is chosen and the Table/Range box reads Claims!$A$1:$F$151.
    The Claims sheet after the paste, and the PivotTable's data source.
  4. Frage 4To list one row per department with its total, where should Department go in the Fields pane?

    Wählen Sie eine Antwort.

Feedback

Beantworten Sie die Fragen, geben Sie Ihre Formel in die Tabelle ein, drücken Sie Enter und dann „Prüfen“. Wir bewerten Ihre Antworten, den Wert in jeder Antwortzelle und die Formel dahinter.

Aufgaben

  1. Nicht geprüftIn G2, turn the amount in F2 into a real number (0 when F2 is empty), then fill the formula down to G151.
  2. Nicht geprüftIn J2, count how many cells in F2:F151 hold text instead of a number.
  3. Nicht geprüftIn J3, add up the cleaned amounts in G2:G151.
  4. Nicht geprüftFrage 1: Which of these make Excel summarize Amount as Count instead of Sum when the field is dropped into Values? Select all that apply.
  5. Nicht geprüftFrage 2: Suppose you change nothing in the data and only pick Sum in Value Field Settings, as pictured. What will Sum of Amount show?
  6. Nicht geprüftFrage 3: A week later, 12 new claims are pasted below the list, in rows 152 to 163 (pictured). After Refresh the report still leaves them out. Which of these make the report include them? Select all that apply.
  7. Nicht geprüftFrage 4: To list one row per department with its total, where should Department go in the Fields pane?

Für Tabellenaufgaben empfehlen wir einen Computer. Die Tabelle scrollt innerhalb dieses Rahmens.

StartInsertFormulasDataView
A1fxIhre Arbeitsmappe wird vorbereitet…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Beantworten Sie die Fragen, füllen Sie die Zellen aus und prüfen Sie.

Über diese Übung

Ein Datensatz mit 150 Zeilen auf einem Tabellenblatt, 3 Antwortzellen, etwa 12 Minuten. Bewertet in Ihrem Browser: der Wert und die Formel dahinter, mit Feedback, das die Korrektur benennt. Dazu kommen 4 Multiple-Choice-Fragen, die ebenfalls mit „Prüfen“ bewertet werden.

So lösen Sie die Aufgabe

  • G2=IF(F2="",0,VALUE(F2))
  • J2=SUMPRODUCT(--ISTEXT(F2:F151))
  • J3=SUM(G2:G151)

Excel sums a value field only when every cell of it is a number. Here some amounts are text (they sit at the left of the cell) and some are blank, so the PivotTable fell back to Count of Amount, which counts every non-empty cell like COUNTA. Picking Sum without cleaning adds the text amounts as 0, so the totals come out too low: convert them first (column G), then summarize by Sum. Department belongs in Rows for one row per department, and rows pasted below a fixed source range stay outside it until you change the data source, or use an Excel Table, which grows with the data.

In desktop Excel you can fix column F in place instead of using a helper column: select F2:F151 and use the warning button next to the green triangles → Convert to Number, or Data → Text to Columns → Finish, or copy a cell that holds 1 and use Paste Special → Multiply. VALUE reads text with your own regional settings; if your Excel uses a comma as the decimal separator, =NUMBERVALUE(F2,".") reads these dot-decimal amounts correctly. Blanks: fill them with 0 (Home → Find & Select → Go To Special → Blanks, type 0, Ctrl+Enter) or keep them and pick Sum yourself. A formula that returns "" for an empty amount returns text, which keeps the field on Count. Then refresh (PivotTable Analyze → Refresh, Alt+F5 on Windows; the tab is called Analyze in Excel 2016 and 2019) and set the field to Sum: right-click a value → Summarize Values By → Sum, or Value Field Settings. Change Data Source sits on the same tab; an Excel Table (Insert → Table) grows with every row added below it, so the PivotTable picks new rows up at the next Refresh. Auto Refresh, which Microsoft lists as a Microsoft 365 Insider feature for now, refreshes PivotTables on workbook data by itself, but it never stretches a fixed range either. This in-browser grid has no PivotTable: the pictures show what Excel does, and your formulas are graded in the grid.

Andere Wege, die funktionieren

  • =F2*1 or =--F2 in G2 — arithmetic turns text into a number and an empty cell into 0
  • =COUNTIF(F2:F151,"*") or =COUNTA(F2:F151)-COUNT(F2:F151) in J2 — other ways to count the text cells
  • =SUMPRODUCT(--F2:F151) in J3 — the clean total in one formula, without column G

Antworten auf die Fragen

Left, the Pivot sheet: A1 Department with a filter drop-down showing (All), A3 Count of Amount, A4 142. Right, the PivotTable Fields pane: fields Claim ID, Date, Employee, Department (checked), Category, Amount (checked); Filters: Department; Columns: empty; Rows: empty; Values: Count of Amount, outlined in green.
The PivotTable as the assistant left it: the report (left) and its Fields pane (right).
  1. Frage 1. Which of these make Excel summarize Amount as Count instead of Sum when the field is dropped into Values? Select all that apply.

    Antworten: Some amounts are stored as text und Some amount cells are blank

    Excel sums a value field only when every cell of it holds a number; text that looks like a number or an empty cell makes it fall back to Count, which counts non-empty cells like COUNTA (142 of the 150 claims here). Where Department sits, decimals and refreshing play no part in that choice.

  2. Frage 2. Suppose you change nothing in the data and only pick Sum in Value Field Settings, as pictured. What will Sum of Amount show?

    Antwort: Too little: the text amounts are added as 0

    A PivotTable adds only real numbers: blank or nonnumeric values become 0 when the field is summed. The text amounts drop out of every total without any warning, so convert them first (your column G) and then summarize by Sum.

  3. Frage 3. A week later, 12 new claims are pasted below the list, in rows 152 to 163 (pictured). After Refresh the report still leaves them out. Which of these make the report include them? Select all that apply.

    Antworten: Change Data Source so the range ends at row 163 und Make the claims an Excel Table and use it as the source

    The PivotTable reads the fixed range Claims!$A$1:$F$151, and Refresh or Refresh All re-reads exactly that range. Change Data Source extends it once; an Excel Table (Insert → Table, Ctrl+T on Windows, ⌘T on a Mac) grows with every row added below it, so each later Refresh picks new claims up.

  4. Frage 4. To list one row per department with its total, where should Department go in the Fields pane?

    Antwort: Drag it to the Rows area

    A field in Rows gives each of its items its own line, here one per department with the amounts summed beside it and a Grand Total at the bottom. Columns would spread the departments across the top, Values would count them, and a report filter shows one total at a time.

Häufige Fehler

Picking Sum in Value Field Settings without cleaning the data: the text amounts are then added as 0 and every department total is too low. Converting with =IF(F2="","",VALUE(F2)) is the other trap: its "" results are text and keep the field on Count.

So bewerten wir

Jede Formel, die das richtige Ergebnis liefert, wird akzeptiert, außer die Aufgabe nennt eine Funktion: Dann bringt eine andere Funktion die halbe Punktzahl. Wir vergleichen Werte mit einer kleinen Toleranz und ignorieren bei Text Groß- und Kleinschreibung sowie Leerzeichen. Eine richtige Zahl, die statt einer Formel von Hand eingetippt wurde, bringt die halbe Punktzahl. Aufgaben mit mehreren Zellen bringen die volle Punktzahl, wenn 100% der Zellen stimmen, und die halbe ab 50%. Wo es darauf ankommt, ändern wir außerdem eine Eingabezelle in einer Kopie Ihrer Tabelle und prüfen, ob Ihre Formel mitzieht.

Antworten nach den Regeln von Excel 365 berechnet und am 20. November 2026 in unserem Bewertungssystem geprüft. Verfasst von GridBelt team.

In diesen Programmen

  • Excel test in 7 days, Plan für Stufe 2Tag 5
  • Excel test in 7 days, Plan für Stufe 3Tag 5
  • Excel for Data AnalystsTag 6

Ähnliche Übungen

  • Read a PivotTable report and check one of its cellsAnfänger, ~8 Min.
  • Share of revenue by channel: % of Grand Total with formulasFortgeschrittene, ~7 Min.
  • Count orders, names and blanks (COUNT, COUNTA, COUNTBLANK)Anfänger, ~4 Min.

Funktionsreferenz

Offizielle Dokumentation

Nächste Übung

Pick the right chart for each question