Office-Blog

SUMMENPRODUKT gehört zu meinen Lieblingsfunktionen. Ich nutze sie häufig zum Auswerten von Listen. SUMMENPRODUKT bietet einfach mehr Flexibilität als ZÄHLENWENNS, SUMMEWENNS oder Pivot-Tabellen, wenn spezielle Kriterien zu berücksichtigen sind. Jetzt habe ich eine weitere Einsatzmöglichkeit für SUMMENPRODUKT entdeckt: ich nutze sie auch bei visuellen Auswertungen.

Hier das Beispiel: Welche Umsätze wurden im letzten Monat mit sog. Auslaufartikeln erwirtschaftet? Dazu sollen in der Umsatzaufstellung automatisch alle Zeilen farbig hinterlegt werden, in denen ein Auslaufartikel steht. Das erledige ich mit einer Bedingten Formatierung, die von SUMMENPRODUKT gesteuert wird.

Links die automatische Kennzeichnung von Umsätzen mit Auslaufartikeln und rechts die zugehörige Liste mit dem Bereichsnamen Auslaufartikel

Automatische Kennzeichnung mit Listenabgleich einrichten

Was sonst nur über SVERWEIS oder VERGLEICH lösbar wäre, erledige ich mit SUMMENPRODUKT in einer kurzen, übersichtlichen Formel. Mit SUMMENPRODUKT kann ich individuelle Kriterien einbauen, ohne die Basisdaten vorher um zusätzliche Spalten für Berechnungen erweitern zu müssen. So geht’s:

• Zuerst markieren ich die Liste der Umsätze, und zwar ohne die Überschriften.
• Dann wähle ich Start > Bedingte Formatierung > Neue Regel > Formel zur Ermittlung der zu formatierenden Zellen verwenden.
• Dort gebe ich folgende Formel ein: =SUMMENPRODUKT(($C7=Auslaufartikel)*1).

Diese Summenproduktformel in der bedingten Formatierung sorgt für die farbige Kennzeichnung von Zeilen mit Auslaufartikeln

• Anschließend klicke ich rechts unten auf Formatieren und wähle im folgenden Dialogfeld im Register Ausfüllen eine passende Füllfarbe (hier Orange).

So funktioniert die Formel

• In der Formel wird $C7 (das $-Zeichen vor dem Buchstaben für die Spalte ist unerlässlich) mit dem Zellbereich Auslaufartikel verglichen.
• Auslaufartikel wurde zuvor als Bereichsname angelegt (Strg+F3).
• Das Ergebnis des Vergleichs ergibt WAHR oder FALSCH und wird mit 1 multipliziert, um eine Zahl für die anschließende Summenbildung zu liefern (auch wenn es in diesem Fall nur um die „Summe“ eines Bereichs mit einer einzigen Zeile geht).
• Ist das Ergebnis der Formel größer als null, wird die Formatierung ausgeführt.

FAZIT: SUMMENPRODUKT liefert eine perfekte und zudem zeitsparende Lösung.

Eine dreistündige Werkstatt zum Aufbau von Info-Cockpits …

… unter Verwendung von SUMMENPRODUKT und weiteren Funktionen führe ich am Vormittag des 27. Oktober bei den Excel-Kompetenztagen in Fulda durch.

Zudem gibt es Dutzende weiterer Kurse zu den Themen Formeln und Funktionen, Diagramme, Pivot-Tabellen, Power Query, Power Pivot, Datenmodell und DAX

Per Klick auf das folgende Bild geht’s zum Flyer der Excel-Kompetenztage.