Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1516to1520
1516to1520
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

=SUMMEWENN(S) selbstfindende Spalte

Forumthread: =SUMMEWENN(S) selbstfindende Spalte

=SUMMEWENN(S) selbstfindende Spalte
27.09.2016 15:46:17
UweD
Hallo
Diesmal brauche ich mal eure Hilfe
Ich habe 2 Tabellen. Zusammenfassung; Bestellungen
In der Ersten sind alle zu untersuchenden Artikel enthalten,
in der Zweiten Informationen zu alle offenen Bestellungen (auch mehr Artikel enthalten)
Die Anordnung der Spalten sind unterschiedlich
Siehe Beispielmappe.
Natürlich sind in der Originaldatei wesentlich mehr Spalten
https://www.herber.de/bbs/user/108454.xlsx
1) Ich suche das MinWenn ohne Leer oder 0 (das MaxWenn hab ich)
2) Die Summewennformeln bekomme ich auch hin, muss dann aber immer die gewünschte Spalte für die Summenbildung angeben..
Wie kann ich es machen, dass die richtige Spalte aus der Überschrift genommen wird.
LG UweD
Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: =SUMMEWENN(S) selbstfindende Spalte
27.09.2016 15:49:18
UweD
Schreibfehler in der Datei:
aus Spalte B

Zusammenfassung
 ABCDEF
1ArtiklenummerBezeichnungkleinstes Datumgrößtes DatumAnzahlBestellwert
21000Testartikel 100.01.190031.12.2016203115
32000Testartikel 200.01.190028.11.2016200100
43000Testartikel 300.01.190031.10.2016315
54000Testartikel 400.01.190000.01.190000
6      
7    aus Spalte Caus Spalte B

verwendete Formeln
Zelle Formel Bereich N/A
C2:C5{=MIN(WENN(UND(Bestellungen!$A:$A<>0;Bestellungen!$A:$A=Zusammenfassung!$A2);Bestellungen!$F:$F;))}$C$2 
D2: D5{=MAX(WENN(Bestellungen!$A:$A=Zusammenfassung!$A2;Bestellungen!$F:$F;))}$D$2 
E2:E5=SUMMEWENN(Bestellungen!$A:$A;Zusammenfassung!$A2;Bestellungen!C:C)  
F2:F5=SUMMEWENN(Bestellungen!$A:$A;Zusammenfassung!$A2;Bestellungen!B:B)  
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!
http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://Hajo-Excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 21.10 einschl. 64 Bit


Anzeige
AW: reicht Dir SUMMENPRODUKT() ? ...
27.09.2016 17:48:05
...
Hallo Uwe,
.... und für die MIN/Max-Datumswerte würde ich AGGREGAT() nutzen.
 ABCDEF
1ArtiklenummerBezeichnungkleinstes Datumgrößtes DatumAnzahlBestellwert
21000Testartikel 126.10.201631.12.2016203115
32000Testartikel 226.10.201628.11.2016200100
43000Testartikel 327.10.201631.10.2016315
54000Testartikel 4  00
6      

Formeln der Tabelle
ZelleFormel
C2=WENNFEHLER(AGGREGAT(15;6;Bestellungen!E$1:E$99/(Bestellungen!A$1:A$99=A2); 1); "")
D2=WENNFEHLER(AGGREGAT(14;6;Bestellungen!F$1:F$99/(Bestellungen!A$1:A$99=A2); 1); "")
E2=SUMMENPRODUKT((Bestellungen!$A$2:$A$99=$A2)*(Bestellungen!$B$2:$C$99)*(Bestellungen!$B$1:$C$1=E$1))
F2=SUMMENPRODUKT((Bestellungen!$A$2:$A$99=$A2)*(Bestellungen!$B$2:$C$99)*(Bestellungen!$B$1:$C$1=F$1))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
danke..
28.09.2016 09:25:16
UweD
Hallo Werner Aggregat: - Was sonst aus deiner Feder. ;-) - Auf das Konstrukt muss man erst mal kommen. Es ist ja schon schwierig, es nachzuvollziehen zu können. Summenprodukt: - ja, damit hatte ich auch schon experimentiert. Leider erfolglos. - ich hab die Formel mal in die Originaldatei übertragen. Leider erfolglos. Es liegt aber daran, dass auch Textspalten dazwischen sind. Schade, dann werde ich die Spalten explizit angeben. Danke für deine Mühe. LG Uwe
Anzeige
AW: musst Du nicht; mit INDEX() und VERGLEICH()...
28.09.2016 12:48:32
...
Hallo Uwe,
... ist in Kombination mit SUMMEWENN() als Matrixfunktion(alität)sformel (infolge INDEX() eh die besserer Variante (performanter) als die SUMMENPRODUKT()-Formel. Hätte ich eigentlich gleich drauf kommen sollen.
=SUMMEWENN(Bestellungen!$A:$A;$A2;INDEX(Bestellungen!$A:$ZZ;;VERGLEICH(G$1;Bestellungen!$1:$1;)))
und Formel nach rechts und unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: musst Du nicht; mit INDEX() und VERGLEICH()...
28.09.2016 14:48:20
UweD
OK, ich bau es gleich mal ein und melde mich.
Bei den MIN / Max Aggregatformeln passt was nicht in der Originaldatei.
Hier werden vorhandene Werte als "" angezeigt
Das suche ich noch. Sind über 800 Zeilen.
LG Uwe
es geht...
28.09.2016 15:11:30
UweD
Hallo nochmal
passt jetzt alles.
Auch bei den Aggregatformeln hab ich den Fehler gefunden.
Die Auswerte- Tabelle hatte ca 800 Zeilen,
deshalb hatte ich die 99 in der Formel schon auf 999 geändert
Aber es gibt Bestellungen bis Zeile 22.500.

Auswertung
 QR
1Kleinster TerminGrößter Termin
204.10.201614.03.2017

verwendete Formeln
Zelle Formel Bereich N/A
Q2=WENNFEHLER(AGGREGAT(15;6;Bestellobligo!R$2:R$30000/(Bestellobligo!$A$2:$A$30000=$A2); 1);"")  
R2=WENNFEHLER(AGGREGAT(14;6;Bestellobligo!S$1:S$30000/(Bestellobligo!$A$1:$A$30000=$A2); 1); "")  
http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://Hajo-Excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 21.10 einschl. 64 Bit


so klappt es
Vielen vielen Dank.
LG Uwe
Anzeige
AW: freut mich auch, danke für die Info owT
28.09.2016 16:32:16
...
Gruß Werner
.. , - ...

Forumthreads zu verwandten Themen

Summewenns
cfrei75

Hallo ich habe folgende Formel: =SUMMEWENNS(C4:CT4;C3:CT3;">6:00";C3:CT3;" Diese funktioniert einwandfrei, möchte aber anstatt die 6:00 und 21:00 händisch reinzuschreiben, dass diese von einem Feld übernommen wird, da diese variabel sind.
Wenn ich aber die Formel wie folgt schreibe =SUMM...
21.10.2008 10:57:16
Thread-ID: 1017894
5 Beiträge
summenprodukt mit 2 wenn's geht nicht?
Silvi


Moin zusammen,
muß eine ziemlich komplexe Abfrage machen und habe mir untenstehende ellenlange Formel ausgedacht, die leider nicht funktionert... :o( Hat von Euch jemand eine Idee, wie man das eleganter lösen könnte (ohne Makro?). Vielleicht ist nur eine Klammer falsch? Ich komm nicht...
23.01.2009 10:30:18
Thread-ID: 1042851
8 Beiträge
SUMMEWENN- Teilstring des Suchkriteriums
Joachim

Guten Morgen,
im Suchbereich befinden sich Texte. Ich brauch aber die Summe derer, die einem Teilstring des Textes entsprechen. Beispiel:
Suchbereich: Summenbereich
2005_SIM_EG 1520
2004_SIM_EG 2588
2003_SIM_EG 3444
2005_SIM_FW 1220
SUMMEWENN(Suchbe...
03.02.2009 08:16:09
Thread-ID: 1046502
8 Beiträge
"summewenn" in Zeiterfassung funktioniert nicht
Gernot

Die Datei https://www.herber.de/bbs/user/61213.xls wurde aus Datenschutzgründen gelöscht
Liebe Forumsexperten
Ich habe so ziemlich alle "summewenn" und "summenprodukt"-Lösungen probiert, die im Forum so gut beschrieben sind. Leider funktioniert keine der Lösungen. Die Exceldatei habe i...
15.04.2009 18:40:21
Thread-ID: 1068933
6 Beiträge
Anzeige
Summewenn mit unzusammenhängendem Suchbereich
Sebastian

Hallo allerseits,
ich verzweifle mit meinem müden KOpf gerade an einem sicherlich trivialen Problem: Ich habe eine Tabelle mit ziemlich vielen Spalten. Die Zellen sind entweder leer oder mit einer 1 befüllt. Ich muss zählen, wie oft die 1 in bestimmten Spalten (also NICHT allen) vorkommt. <...
21.04.2009 12:13:29
Thread-ID: 1070011
16 Beiträge
Summewenn mehrer Bedingungen versch. Blätter
Aquestor

Hallo zusammen,
ich habe folgendes Problem:
Ich möchte im Datenblatt Whiteboard in Zelle C11 überprüfen, ob in den Datenblättern Ma_1....Ma_18 Zelle C11 ein Wert steht. Steht in einer der 18 Zellen die überprüft werden ein Wert, so sollen Ma1!C11+Ma_2!C11+.....Ma_18!C11 summiert werden....
26.05.2009 11:23:28
Thread-ID: 1077310
9 Beiträge
summewenn + Bedingg. "die ersten 5 linken Zeichen"
Matthias

Hallo zusammen,
ich möchte die ersten 5 linken Zeichen (Text) einer Zelle auswerten. Wenn ein Kriterium erfüllt ist, soll summiert werden.
Das Kriterium besteht aus Text. Die Länge kann variieren.
Für Anregungen für eine korrekte Formel bin ich dankbar :-).
TIA und Grüße
Mat...
01.07.2009 12:35:51
Thread-ID: 1085662
3 Beiträge
Anzeige
Summewenn mit ISTNV
chris

Hallo Forumsgemeinde,
ich arbeite mit einer englischen Excelversion und erst beim Öffnen in einer deutschen Version ist mir ein Fehler aufgefallen. Im Englischen wird als Fehlercode #N/A ausgegeben, was im Deutschen #NV entspricht. Nun habe ich leider in einer SUMMEWENN-Formel (SUMIF) die B...
19.07.2009 13:33:06
Thread-ID: 1089769
7 Beiträge
Summewenn mit mehreren Suchkriterien
Pfannen-Meister

Guten Morgen,
ich bräuchte bei meinem Excel-Problem Unterstützung und fände es klasse, wenn mir jemand helfen könnte. Dazu kurz ein Beispiel:
T-Shirts Farbe Kleidergröße
5 rot 48
9 blau 48
3 blau 54
4 grün 52
8 gelb 56
21.07.2009 09:40:01
Thread-ID: 1090130
3 Beiträge
Summe in L wenn C übereinstimmt?
Andy

Hallo zusammen,
weiß jemand wie ich meine Summe in L4:L1000 zählen kann,
wenn ich nur die Werte mit einbeziehen möchte, die in der selben Zeile in C4:C1000 stehen und mit dem text in A1 übereinstimmen.
?
Vielen Dank
Andy
03.11.2009 14:24:40
Thread-ID: 1114154
10 Beiträge
summewenn Fkt. als Makro
meixner

Hallo zusammen,
habe ein Problem mit einem Makro. Anhand eines Makros wird eine Tabelle kopiert mit range(AX :BA) und in eine andere Excel - Mappe kopiert. Ich möchte, aber das dieses Makro gleichzeitig vor kopieren eine summewenn - Funktion ausführt.
Die Fkt. heisst: summewenn(AX:AX; A...
29.11.2009 17:32:32
Thread-ID: 1120809
2 Beiträge
summewenn - zwei bedingungen - zwei Summenbereiche
Loewelutz

Liebe Ex(cel)perten,
ich benötige Hilfe beim Formelaufbau für folgende Konstellation:
1. Bereich: B13:B27
- Bedingung: 1
- Summenbereich: f13:f27
2. Bereich: e13:b27
- Bedingung: 1
- summenbereich: h13:h27
Erläuterung:
Wenn im 1. Bereich das Suchkriterium "1"...
02.12.2009 15:00:20
Thread-ID: 1121425
3 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Dynamische Summen mit SUMMEWENN(S) und INDEX in Excel

Schritt-für-Schritt-Anleitung

  1. Daten vorbereiten: Stelle sicher, dass Du zwei Tabellen hast: eine für die Zusammenfassung und eine für die Bestellungen. Die Spalten sollten entsprechend angeordnet sein, wobei die Überschriften identisch benannt sind.

  2. Formel erstellen: Um eine dynamische Summenbildung zu erreichen, nutze die Formel:

    =SUMMEWENN(Bestellungen!$A:$A; Zusammenfassung!$A2; INDEX(Bestellungen!$A:$ZZ;;VERGLEICH(G$1;Bestellungen!$1:$1;)))

    Diese Formel sucht den passenden Wert in der Bestellungen-Tabelle und summiert die entsprechenden Werte dynamisch, basierend auf der Überschrift.

  3. Formel kopieren: Ziehe die Formel nach rechts und unten, um sie auf die anderen Zellen anzuwenden. Achte darauf, dass die Zellreferenzen korrekt angepasst werden.

Häufige Fehler und Lösungen

  • Fehler: Falsche Spaltenreferenzen
    Stelle sicher, dass die Spalten in der Bestellungen-Tabelle korrekt referenziert sind, insbesondere wenn TEXT-Spalten dazwischen liegen.

  • Fehler: #WERT! oder #NV
    Dies kann auftreten, wenn die Vergleichswerte in der Überschrift nicht übereinstimmen. Überprüfe die Spaltenüberschriften auf Tippfehler.

  • Lösung für leere Zellen: Wenn Du leere Zellen oder Nullen ignorieren möchtest, kannst Du die Formel mit WENNFEHLER erweitern:

    =WENNFEHLER(SUMMEWENN(...); 0)

Alternative Methoden

  • AGGREGAT(): Eine weitere Möglichkeit zur Summenberechnung ist die Verwendung der AGGREGAT()-Funktion. Diese kann verwendet werden, um MIN- und MAX-Werte zu berechnen, während sie Fehler ignoriert:

    =AGGREGAT(9; 6; Bestellungen!F:F/(Bestellungen!A:A=Zusammenfassung!A2))

  • SUMMENPRODUKT(): Eine fortgeschrittene Methode, die mehrere Bedingungen gleichzeitig verarbeiten kann. Beispiel:

    =SUMMENPRODUKT((Bestellungen!$A$2:$A$99=$A2)*(Bestellungen!$B$2:$C$99)*(Bestellungen!$B$1:$C$1=G$1))

Praktische Beispiele

  • Beispiel für SUMMEWENN(): Angenommen, Du hast in der Tabelle "Bestellungen" die Spalte A für Artikelnummen und die Spalte B für Bestellwerte. Die Formel könnte wie folgt aussehen:

    =SUMMEWENN(Bestellungen!$A:$A; Zusammenfassung!$A2; Bestellungen!B:B)

  • Beispiel für INDEX und VERGLEICH: Um den richtigen Summenbereich dynamisch zu bestimmen, kannst Du die Formel:

    =SUMMEWENN(Bestellungen!$A:$A; Zusammenfassung!$A2; INDEX(Bestellungen!$B:$D;;VERGLEICH("Bezeichnung";Bestellungen!$1:$1;0)))

Tipps für Profis

  • Verwende benannte Bereiche: Damit behältst Du den Überblick und kannst die Formeln leichter lesen.
  • Matrixformeln: Nutze Matrixformeln, wenn Du mit großen Datenmengen arbeitest, um die Leistung zu steigern. Schließe die Eingabe mit Strg + Umschalt + Enter ab.
  • Excel-Version: Einige Funktionen, wie AGGREGAT, sind ab Excel 2010 verfügbar. Stelle sicher, dass Du eine unterstützte Version verwendest.

FAQ: Häufige Fragen

1. Frage
Wie kann ich SUMMEWENN() mit mehreren Kriterien verwenden?
Antwort: Du kannst SUMMEWENN() nicht direkt für mehrere Kriterien verwenden, aber Du kannst SUMMENPRODUKT() oder die Kombination von WENN() und SUMMEWENN() nutzen.

2. Frage
Gibt es eine Möglichkeit, leere Zellen in der Berechnung zu ignorieren?
Antwort: Ja, Du kannst die WENNFEHLER()-Funktion verwenden, um leere Zellen zu behandeln und gegebenenfalls einen alternativen Wert zurückzugeben.

3. Frage
Wie funktioniert der INDEX-VERGLEICH in Verbindung mit SUMMEWENN()?
Antwort: Der INDEX-VERGLEICH hilft dabei, den richtigen Bereich zu bestimmen, aus dem die Summenwerte entnommen werden, basierend auf der Überschrift. Dies macht die Berechnung flexibler und dynamischer.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige