Zellen auf Basis anderer Zellen summieren

Question

Ich habe beispielsweise folgende Excel-Datei:

| USER ID | SCORE | TOTAL SCORE | TOTAL BONUS | NOTE  |
| ------- | ----- | ----------- | ----------- | ----- |
| 18      | 10    |             |             |       |
| 18      | 25    |             |             |       |
| 18      | 15    |             |             |       |
| 18      | 5     |             |             | bonus |
| 18      | 30    |             |             |       |
| 24      | 2     |             |             |       |
| 24      | 7     |             |             |       |
| 24      | 21    |             |             |       |
| 32      | 6     |             |             |       |
| 32      | 9     |             |             | bonus |
| 32      | 18    |             |             |       |
| 32      | 40    |             |             |       |

Für jede BENUTZER-ID muss ich in der ersten Zeile die Gesamtsumme der Punktzahlen ohne die Boni (erkennbar an der Spalte „ANMERKUNG“) und separat die Summe der Boni (sofern vorhanden, andernfalls ist sie 0) angeben.

Das Ergebnis des Beispiels sollte folgendes sein:

| USER ID | SCORE | TOTAL SCORE | TOTAL BONUS | NOTE  |
| ------- | ----- | ----------- | ----------- | ----- |
| 18      | 10    | 80          | 5           |       |
| 18      | 25    |             |             |       |
| 18      | 15    |             |             |       |
| 18      | 5     |             |             | bonus |
| 18      | 30    |             |             |       |
| 24      | 2     | 30          | 0           |       |
| 24      | 7     |             |             |       |
| 24      | 21    |             |             |       |
| 32      | 6     | 64          | 9           |       |
| 32      | 9     |             |             | bonus |
| 32      | 18    |             |             |       |
| 32      | 40    |             |             |       |

Ist es möglich die Summenberechnung mittels einer Funktion zu automatisieren? Vielleicht mit SUMIF()?

Answer 1

Tabelle beginnt inA1

Total Score:  C2: =IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13)  -SUMIFS($B$2:$B$13,$A$2:$A$13,A2,$E$2:$E$13,"bonus"))

Total Bonus:  D2: =IF(A2=A1,"",SUMIFS($B$2:$B$13,$A$2:$A$13,A2,$E$2:$E$13,"bonus"))

und nach unten auffüllen.

Answer 2

Mit etwas Hilfe vonDieser Artikel:

Sie müssen einige zusätzliche Spalten erstellen. Ich habe zuerst die Gesamtpunktzahl für die Studenten-ID (einschließlich Bonus) berechnet, dann den Gesamtbonus und zuletzt den Gesamtbonus von der Gesamtpunktzahl abgezogen, um das gewünschte Ergebnis zu erhalten.

• Wir berechnen die Gesamtpunktzahl und den Gesamtbonus mit der SUMPRODUCTFunktion: SUMPRODUCT((A2:A13=A2)*B2:B13) Die erste Matrix sind die Benutzer-IDs, nach denen die Ergebnisse gruppiert und summiert werden. Die zweite Matrix ist die Benutzerpunktzahl.
• Wir erstellen eine Zeile, in der der Gesamtbonus mit einer WENN-Funktion berechnet wird:IF(F2="bonus",B2,0)
• Wir berechnen den Gesamtbonus, ganz ähnlich der ersten Funktion: SUMPRODUCT((A2:A13=A2)*D2:D13)
• Wir ziehen den Gesamtbonus von der Gesamtpunktzahl ab:C2-E2

Danach muss die Tabelle nur noch manuell bereinigt werden.

Es besteht auch die Möglichkeit, die Zellen so zu formatieren, dass sie leer sind, wenn ihr Wert gleich 0 ist (das könnten Sie auf die Spalte „Bonus“ anwenden).

Answer 3

Versuchen Sie diese Formel:

GESAMTPUNKTZAHLSpalte:

=IF(A2=A1,"",SUMPRODUCT(($A$2:$A$13=A2)*$B$2:$B$13))

GesamtbonusSpalte:

=IF(A2=A1,"",SUMPRODUCT(N($A$2:$A$13=A2)))

Answer 4

Wie es funktioniert:

• Formel in Zelle D2:

  =IF(A2=A1,"",SUMPRODUCT(($A$2:$A$13=A2)*($E$2:$E$13="bonus"),$B$2:$B$13))
  

• Formel in Zelle C2:

  =IFERROR(IF(A2=A1,"",SUMIF(A$2:A$13,A2,B$2:B$13))-D2,"")
  

Sie können die Zellbezüge in der Formel nach Bedarf anpassen.