Office-Blog

Mit SUMMENPRODUKT lassen sich auch visuelle Auswertungen steuern

11.08.2020   Hildegard Hügemann

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.

Über den Autor

Hildegard Hügemann

- Dipl.-Informatikerin
- Zertifizierte Office-Trainerin mit den Schwerpunkten Excel, Access, Word
- Entwicklerin von Excel-Tools zur Projekt-, Produktions- und Verkaufssteuerung
- Coach bei der Datenaufbereitung mit Access, Power Query und Power Pivot
- Entwicklerin von Access-Datenbanken für Mittelständler und Behörden
- Bloggerin zu Excel und Access auf www.huegemann-informatik.de
- Autorin für Microsoft Press, dpunkt.verlag und das »Projekt Magazin«

2 Comments so far

Christian NeubergerPosted on  8:05 pm - Aug 18, 2020

Hallo Frau Hügemann,

Oder($C3=Auslaufartikel)
ist am einfachsten.

Viele Grüße

Christian Neuberger

Frank Arendt-TheilenPosted on  10:11 am - Aug 17, 2020

Hallo Hildegard,
prima Beitrag und der Einsatz der Funktion SUMMENPRODUKT() mal in einer anderen Einsatzumgebung.
Ich habe noch eine weitere Möglichkeit gefunden, allerdings mit der Funktion SUMME():
=SUMME(($C3=Auslaufartikel)*1)
Mit freundlichem Gruß aus der Rattenfängerstadt Hameln
Frank Arendt-Theilen

Schreibe eine Antwort