Andreas Unkelbach
Werbung


Mittwoch, 9. Mai 2018
17:44 Uhr

SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen

Wie schon am Ende im Artikel "Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt" angekündigt sind die Verweisfunktionen (SVERWEIS oder auch INDEX,VERGLEICH) eingeschränkt wenn mehr als eine Übereinstimmung übernommen werden soll.

Hier bietet sich dann tatsächlich die Formel SUMMEWENN an.

Anhand des Syntax

=summewenn(BEREICH;SUCHKRITERIEN;SUMMEBEREICH)

hatte ich die Hoffnung, dass als Summenbereich tatsächlich mehr als eine Spalte genommen werden kann und so habe ich meine Hochrechnung als Tabelle formatiert und als Hochrecnung benannt.

Um ein einfaches Beispiel zu haben sieht diese Tabelle wie folgt aus:

Tabelle T_Hochrecnung

Hier sind für einzelne Innenaufträge mehrere Hochrechnungen erfasst. Da es sich dabei aber um eine Hochrechnung von Personalkosten handelt kann ein Innenauftrag auch mehrfach vorkommen. Im Beispiel bekommt hier Andreas sowohl in Zeile 3 als auch Zeile 8 entsprechende Zahlungen. Ziel der Auswertung ist nun die zu erwarteten Kosten für Mai bis Dezember für den Innenauftrag Andreas zu erhalten.

Mein erster Versuch war hier tatsächlich statt SVERWEIS oder INDEX die Formel SUMMEWENN.

=SUMMEWENN(T_Hochrechnung[Innenauftrag];D3;T_Hochrechnung[[Mai]:[Dezember]])

Hier sollte eine Summe über die Spalten Mai bis Dezember für den Eintrag Andreas  beziehungsweise das Suchkriterium in der Zelle D3 erstellt werden.

Das Ergebnis von 10, wie in folgender Abbildung zu sehen war dann aber enttäuschend.

SUMMEWENN Fehler

Offensichtlich kann der Summenbereich nur eine Spalte umfassen.

Zum Glück gibt es aber die Seite excelformeln.de auf der dann eine Lösung für mein Problem zu finden war.

Mal wieder eine Matrixfunktion, die im Beitrag "Nach Suchkriterium suchen und mehrere Spalten summieren (193)" erklärt wird.

In folgender Abbildung ist dann auch tatsächlich der zutreffende Wert von 80 berechnet worden.

Summe als Matrixformel

Wichtig ist dabei, dass die Formel

=SUMME((T_Hochrechnung[Innenauftrag]=D3)*T_Hochrechnung[[Mai]:[Dezember]])

durch die Tastenkombination STRG + SHIFT und ENTER

als Matrixformel gespeichert wird.

Der Trick funktioniert dabei wie folgt:
Im ersten Teil der Summenfunktion wird geprüft ob die Spalte Innenauftrag mit der Zelle D3 übereinstimmt. Ist dieses der Fall ist das Ergebnis 1 (WAHR) sonst 0 (Falsch) dieses wird dann mit den Werten der Spalte Mai bis Dezember multipliziert, so dass hierdurch ein Ergebnis erzielt wird.

Der Nachteil dieser Vorgehensweise ist jedoch, dass man bei Änderung des Berichtszeitraum hier ebenfalls daran denken muss, dass es sich um eine Matrixformel handelt und diese mit der Tastenkombination angepasst werden muss, so dass aus Versehen diese, sofern nicht als Matrixformel abgeschlossen, einen Fehler hervorruft.

Wenn die Formel nicht als Matrixformel abgeschlossen wird ist das Ergebnis #WERT! (Fehler in Wert) und das mag auch niemand.

Fehler in Wert

Nur als Matrixformel erscheint die {} im Formelfeld (ein manuelles Eingeben dieser Klammer hilft leider auch nichts).

Um etwaige Fehler zu vermeiden habe ich mich dazu entschlossen die Tabelle Hochrechnung um eine weitere Spalte zu erweitern:

Berichtszeitraum

Das schöne an "Als Tabelle formatierte" Tabellen ist ja, dass solche Formeln auch automatisch für jede Zeile mit ausgefüllt werden.

Damit habe ich also für jeden Innenauftrag die Formel
=SUMME(T_Hochrechnung[@[Mai]:[Dezember]])

und kann nun mit Summewenn wieder arbeiten, da ich hier ja nur nach einer Spalte suchen muss.

Summewenn mit Bereichsauswahl

Durch die Unterstützung von Excel kann ich sogar direkt die Spalte Berichtszeitraum auswählen und erhalte so die Formel:

=SUMMEWENN(T_Hochrechnung[Innenauftrag];D3;T_Hochrechnung[Berichtszeitraum])

Summewenn mit mehrere Spalten als Ergebnis

Diese Variante ist dann tatsächlich noch etwas einfacher als eine Matrixformel, auch wenn eine solche natürlich etwas cleverer ist.

Insgesamt erleichtert der Umgang mit der Funktion "Als Tabelle formatieren", wie im Artikel ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten", an vielen Stellen der Zugang auch zu komplexeren Formeln und Funktionen :-)

Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Espresso Tutorial - die digitale SAP Bibliothek

Tags: Excel

Ein Kommentar - - Office

Artikel datenschutzfreundlich teilen

🌎 Facebook 🌎 Twitter 🌎 Google
Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Kommentare

Anonym am 7.8.2018 um 10:59 Uhr
Danke!


Auch kommentieren?


Beim Versenden eines Kommentars wird mir ihre IP mitgeteilt. Diese wird jedoch nicht dauerhaft gespeichert; die angegebene E-Mail wird nicht veröffentlicht: beim Versenden als "Normaler Kommentar" ist die Angabe eines Namen erforderlich, gerne kann hier auch ein Pseudonyme oder anonyme Angaben gemacht werden (siehe auch Kommentare und Beiträge in der Datenschutzerklärung).

Eine Rückmeldung ist entweder per Schnellkommentar oder (weiter unten) als normalen Kommentar möglich. Eine persönliche Rückmeldung (gerne auch Fragen zum Thema) würde mich sehr freuen.

Schnellkommentar (Kurzes Feedback, ausführliche Kommentare bitte unten als normaler Kommentar)

Name (sofern kein Name angegeben wird erscheint Anonym):



Ich nutze zum Schutz vor Spam-Kommentaren (reine Werbeeinträge) eine Wortliste, so dass diese Kommentare nicht veröffentlicht werden. Sollte ihr Kommentar nicht direkt veröffentlicht werden, kann dieses an einen entsprechenden Filter liegen.

Im Zweifel besteht auch immer die Möglichkeit eine Mail zu schreiben oder die sozialen Medien zu nutzen. Meine Kontaktdaten finden Sie auf »Über mich«. Ansonsten antworte ich tatsächlich sehr gerne auf Kommentare und freue mich auf einen spannenden Austausch.

Normaler Kommentar

Name:

E-Mail (wird nicht veröffentlicht und ist nicht erforderlich):

Homepage:

Kommentar:





* Amazon Partnerlink
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
Hinauf




Werbung



Logo Andreas-Unkelbach.de
© 2004 - 2018 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter Google+

»Schnelleinstieg ins SAP Controlling (CO)« und »Berichtswesen im SAP ® ERP Controlling«
Privates

Kaffeekasse 📖 Wunschliste