So verwenden sie subtotal mit sumproduct in excel

Mit der folgenden Formel können Sie die Funktionen ZWISCHENSUMME und SUMMENPRODUKT in Excel kombinieren:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

Mit dieser speziellen Formel können Sie das Produkt der Werte des Bereichs C2:C11 und des Bereichs D2:D11 summieren, selbst nachdem dieser Zellbereich auf irgendeine Weise gefiltert wurde.

Das folgende Beispiel zeigt, wie diese Formel in der Praxis angewendet wird.

Beispiel: Verwendung von SUBTOTAL mit SUMPRODUCT in Excel

Angenommen, wir verfügen über den folgenden Datensatz, der Informationen über den Verkauf verschiedener Produkte in zwei verschiedenen Lebensmittelgeschäften enthält:

Als Nächstes filtern wir die Daten, um nur die Zeilen anzuzeigen, deren Wert in der Spalte „Store“ B ist.

Markieren Sie dazu den Zellbereich A1:D11 . Klicken Sie dann im oberen Menüband auf die Registerkarte „Daten“ und dann auf die Schaltfläche „Filter“ .

Klicken Sie dann auf den Dropdown-Pfeil neben „ Speichern “ und stellen Sie sicher, dass nur das Kontrollkästchen neben „ B“ aktiviert ist. Klicken Sie dann auf „OK“ :

Die Daten werden automatisch gefiltert, um nur Zeilen anzuzeigen, in denen die Store-Spalte gleich B ist:

Wenn wir versuchen, die Funktion SUMRPODUCT() zu verwenden, um das Produkt der Spaltenwerte „Sales“ und „Price“ zu summieren, wird tatsächlich die Summe des Produkts dieser beiden Spalten im Originaldatensatz zurückgegeben:

Stattdessen müssen wir die folgende Formel verwenden:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

Der folgende Screenshot zeigt, wie diese Formel in der Praxis angewendet wird:

Diese Formel gibt die korrekte Summe von 77,5 zurück.

Wir können bestätigen, dass dies korrekt ist, indem wir die Summe des Produkts der Werte zwischen den Spalten „Sales“ und „Price“ manuell berechnen:

Summe des Produkts der Werte zwischen Umsatz und Preis: (3*2) + (12*2,5) + (5*3,5) + (8*3) = 77,5 .

Zusätzliche Ressourcen

In den folgenden Tutorials wird erläutert, wie Sie andere gängige Vorgänge in Excel ausführen:

So löschen Sie gefilterte Zeilen in Excel
So zählen Sie gefilterte Zeilen in Excel
So summieren Sie gefilterte Zeilen in Excel