Sie möchten in Excel nur bestimmte Werte summieren (nicht so einfach wie SUMIF)?

Question

Ich habe also zwei Spalten mit benannten Programmen und eine mit Kostenwerten. Die drei Programme sind ABC, A, B und C. Ich möchte die Kosten aller Programme summieren, die A enthalten. Alle, die B enthalten. Und alle, die C enthalten. ABC ist natürlich in allen Summen enthalten. Das Problem ist, dass die Kalkulationstabelle einen Filter enthält, der die Summe durcheinander bringt, um nur diese Programme zu erhalten. Kann mir jemand helfen? Hier ist ein Beispiel für das, was ich meine:

program     cost
A           5.00
B           4.00
ABC         9.00
A           2.00

Ich möchte also in drei separaten Zellen "Summe mit A"=16,00, "Summe mit B"=13,00, "Summe mit C"=9,00.

Answer 1

Item | Total
A    | 16
B    | 13
C    | 9

Angenommen, Ihr oben genannten Bereich ist in A1:B5, meine erste Formel ist die folgende Array-Formel:

{=SUM(IF(ISERROR(FIND(B6,$A$1:$A$5)),0,$B$1:$B$5))}

Sie erstellen eine Array-Formel, indem Sie die Formel eingeben und die Taste Ctrl+Shift Tasten, während Sie die Eingabetaste drücken. In meiner Lösung habe ich einen Bereich erstellt, in dem ich nach Summen rechne und eine Spalte (in diesem Fall Item genannt) habe, die den Buchstaben angibt, den ich in der ursprünglichen Spalte A sehe.

Wenn Sie versuchen würden, dies mit VBA einzugeben, würden Sie die FormulaArray Eigentum:

Selection.FormulaArray ="SUM(IF(ISERROR(FIND(B6,$A$1:$A$5)),0,$B$1:$B$5))"

Update

Die Beschränkung der Berechnung auf die sichtbaren Zellen ist etwas komplizierter. Nehmen wir an, Ihre Originaldaten befinden sich in den Zellen A1:B5. Nehmen wir außerdem an, dass unsere Testwerte in Zelle C7 beginnen (diagonal zu den Ausgangsdaten). Unsere Summenformel würde dann wie folgt aussehen:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1),0,1)), --NOT(ISERROR(FIND(C7,$A$1:$A$5))), $B$1:$B$5)

Der folgende Teil gibt einen Bereich über die Zellen zurück

OFFSET($B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1),0,1)

Dieser Teil liefert 1 für jede sichtbare Zelle und 0 für unsichtbare Zellen

SUBTOTAL(3,OFFSET($B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1),0,1))

Dieser Teil ist unser Kriterium. NOT(ISERROR(... wird TRUE oder FALSE zurückgeben. Das doppelte negative Vorzeichen -- wandelt diesen Wert in eine negative Ganzzahl um und entfernt dann die Negation.

--NOT(ISERROR(FIND(C7,$A$1:$A$5)))

Schließlich ist die SUMPRODUCT Funktion multipliziert die übereinstimmenden Arrays miteinander und führt die Summe aus. Die ersten beiden Arrays geben eine Reihe von 0 oder 1 zurück. Wenn die Zeile sowohl sichtbar ist als auch unseren Kriterien entspricht, dann erhalten wir 1*1 multipliziert mit dem angegebenen Wert in der Zelle. Wenn die angegebene Zelle nicht sichtbar ist oder nicht den Kriterien entspricht, gibt eines der beiden Arrays eine Null zurück und löscht das gesamte Element.