Summieren von Werten aus einer Spalte basierend auf einer Übereinstimmung in einer anderen Spalte und dem ersten eindeutigen Vorkommen eines Werts in einer dritten Spalte

Question

Ich versuche, eine formelbasierte Lösung zu finden, um die Werte in einer Spalte basierend auf der Übereinstimmung mit einem Wert in einer anderen Spalte zu summieren, jedoch nur für unterschiedliche Vorkommen von Werten in einer dritten Spalte.

Hier ist eine vereinfachte Beispieltabelle:

Ich muss den Buchstaben in Spalte F (A, B, C) mit der Spalte „Buchstaben“ B abgleichen und dann den Wert in Spalte „Werte“ D einmal pro eindeutiger Zahl in Spalte „Zahlen“ C summieren und diese Summe in Spalte G „Summe der Werte“ anzeigen.

Die korrekte Summe wird in den Zellen der Spalte G angezeigt, aber ich habe keine Formel, um dies zu erreichen. Jede Hilfe wäre willkommen!

Answer 1

Bei dieser Art von Problem ist es hilfreich, in Arrays zu denken.

Wenn Sie ein Array (eine Liste) der Zahlen in „Werte“ (Spalte D) erhalten können, wobei „Buchstaben“ (Spalte B) „A“ ergibt und „Zahlen“ von Duplikaten befreit ist, können Sie das Array einfach summieren, um die Antwort zu erhalten.

Dieser Ausdruck:

(B$2:B$12=F2)

gibt ein Array von True/FalseWerten aus, bei Truedem Spalte B immer "A" ist. Dieses hier:

(C$2:C$12<>C$3:C$13)

gibt ein Array von True/FalseWerten Trueaus, wobei eine Zelle in Spalte C nicht mit der folgenden Zelle identisch ist. Da Ihre Duplikate in aufeinanderfolgenden Zellen liegen (kommentieren Sie unten, wenn dies nicht immer der Fall sein wird), enthält dieses Array die FalseStellen, an denen ein Wert zusätzlich vorkommt, und filtert im Wesentlichen die Duplikate heraus. Multipliziert man diese beiden Arrays miteinander, erhält man:

(B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)

wandelt True/FalseWerte in 1en und 0en um und gibt ein Array mit 1 an den Positionen aus, die in der Summe vorkommen sollen. Verwenden Sie dieses Array als in logical_testund IF()Spalte D als value_if_true:

IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12)

gibt ein Array der Werte in Spalte D zurück, wo immer eine 1 war, dazwischen überall, Falsewo eine Null war. Jetzt können wir das Array einfach summieren. Diese Formel, ausgefüllt aus G2, ergibt die unten gezeigten Ergebnisse.

=SUM(IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12))

Beachten Sie, dass dies eine Array-Formel ist und mit eingegeben werden muss CTRLShiftEnter.

Answer 2

Wenn Sie Ihren Daten Spalten hinzufügen können, funktioniert Folgendes für das angegebene einfache Beispiel:

1. Fügen Sie eine Formel hinzu, um die Buchstaben und Zahlen zu verketten. Ich habe für diese Formel Spalte A verwendet, also Zelle A2: =B2&C2
2. Ziehen Sie diese Formel nach unten, um sie auf die Zellen A2:A12 anzuwenden
3. Fügen Sie eine Formel hinzu, um zu testen, ob jede Buchstaben- und Zahlenkombination die erste eindeutige Kombination ist. Ich habe für diese Formel die Spalte E verwendet, also Zelle E2: =ZÄHLENWENN(A$2:A2,A2)
4. Ziehen Sie diese Formel nach unten, um sie auf die Zellen E2:E12 anzuwenden
5. Verwenden Sie die folgende Formel in H2, um die Werte zu summieren, bei denen der Buchstabe übereinstimmt, aber nur für die erste Verkettung von Buchstaben und Zahlen, also Zelle G2: =SUMIFS($D$2:$D$12,$B$2:$B$12,F2,$E$2:$E$12,1)
6. Ziehen Sie diese Formel nach unten, um sie auf die Zellen G2:G4 anzuwenden