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

Summewenns ohne Duplikate

Forumthread: Summewenns ohne Duplikate

Summewenns ohne Duplikate
30.05.2021 19:01:46
Sandra
Hallo!
Wie kann man doppelte Werte (von einer Spalte) in einer Summewenns ausschließen?
Muss man hier mit Summenprodukt arbeiten?
https://www.herber.de/bbs/user/146441.xlsx
Ich möchte die Summe von Spalte C, wenn Datum in einem Zeitraum, jedoch nur einmal gezählt, falls öfters vorkommt.
Bitte um eure Hilfe! Danke!
LG
Sandra
Anzeige

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: dafür nutze SUMMENPRODUKT() & VERGLEICH() ...
30.05.2021 19:46:02
neopa
Hallo Sandra,
... wenn echte Duplikate (also Nr & Datum & BBA sind identisch) nicht gewertet werden sollen, dann so:
=SUMMENPRODUKT(C2:C999;(B2:B999&gt=G1)*(B2:B999&lt=G2)*(VERGLEICH(A2:A999&B2:B999&C2:C999;A2:A999&B2:B999&C2:C999;0)=ZEILE(A2:A999)-1))
Etwas anders wäre es, wenn z,B. als Duplikate nur Daten mit gleicher Nr. oder Nr und Datum aber verschiedenen BBA und Datum oder BBA gewertet werden sollen. Dann muss die Verkettung in dem VERGLEICH()-Formelteil entsprechend reduziert werden.
Gruß Werner
.. , - ...
Anzeige
AW: dafür nutze SUMMENPRODUKT() & VERGLEICH() ...
01.06.2021 13:53:18
Sandra
Hallo, danke! echte Duplikate sind Nr & Datum, habe es angepasst und es funktioniert. Allerdings noch eine Frage dazu:
Wenn ich es richtig sehe dann wird von den Duplikaten immer das erste gefundene Ergebnis gewertet und der Rest eben nicht.
Kann man die Formel noch so anpassen dass er den höchsten Wert von BBA verwendet?
Also zum Beispiel gibt es die Nummer 7778 mehrfach mit dem Datum 01.03.2021 aber immer mit BBA 1, wenn jetzt darüber noch eine Zeile mit Nummer 7778, Datum 01.03.201 aber mit BBA 0,3 gibt, dann würde er 0,3 in der Berechnung verwenden. Könnte man die Formel noch so ändern, dass der höchste Wert von BBA verwendet wird - also 1?
danke für eure Hilfe!
Anzeige
AW: das hast Du richtig erkannt und ...
01.06.2021 14:14:18
neopa
Hallo Sandra,
... die einfachste Lösung für das was Du nun anstrebst wäre, Du nutzt weiter die Formel, die Du angepasst hast für "Nr" und "Datum" als Duplikate und sortierst lediglich Deine auszuwertende Datentabelle nach der Spalte BBA abwärts.
Gruß Werner
.. , - ...
Anzeige
AW: das hast Du richtig erkannt und ...
01.06.2021 14:16:58
Sandra
Hallo Werner, das war auch mein Gedanke aber die Liste ist im Original weitaus umfangreicher und wird laufend ergänzt. Das würde bedeuten dass bei jeder Änderung sortiert werden muss. Würde dir sonst noch etwas einfallen?
AW: auf die Schnelle ...
01.06.2021 14:25:53
neopa
Hallo Sandra,
... fällt mir als eine noch relativ einfache Lösung dazu ein, in einer Hilfsspalte den Maximalwert des BBA zu jeder "Nr" zu ermitteln. Aber Achtung dieser sollte auch nur einmal ausgegeben werden (was sich in der Formel einfach einbauen lässt) . Wenn Du das getan hast, kannst Du dann mit einer einfachen SUMMEWENNS()-Formel die Hilfsspaltenwerte summieren.
Gruß Werner
.. , - ...
Anzeige
AW: auf die Schnelle ...
01.06.2021 15:55:42
Sandra
war mir jetzt nicht sicher was du meinst. ich habe diese Hilfsspalte erstellt: =MAX(WENN($A$2:$A$9=A2;$C$2:$C$9))
das wäre dann meine neue BBA Spalte, also nur noch die Summenprodukt Formel abgeändert, sodass er die Summe von der neuen Hilfsspalte verwendet.
lieber wäre es mir ohne Hilfsspalte gewesen aber hab keinen Plan wie das gehen soll..
Danke für deine Hilfe
LG
Anzeige
AW: nein, MAX() allein hilft nicht ...
01.06.2021 17:45:14
neopa
Hallo Sandra,
... denn wenn für eine Nummer es verschiedene Datumswerte gibt oder bei ebenfalls gleichem Datum und gleichem max. BBA-Wert würde dieser dann wieder doppelt gewertet.
Deshalb als Hilfsspaltenformel z.B. in D2 folgende:
=WENNFEHLER(AGGREGAT(14;6;C$2:C$99/(A$2:A$99=A2)/(B$2:B$99=B2)/(ZÄHLENWENNS(A$2:A2;A2;B$2:B2;B2)=1);1);"") und diese nach unten kopieren.
Als Auswertungsformel reicht dann dafür: =SUMMEWENNS(D:D;B:B;"&gt="&G1;B:B;"&lt="&G2)
Allein an der aufgezeigten Hilfsspaltenformel kannst Du sicherlich ahnen, dass eine Auswertung ohne diese Hilfsspalte entsprechend noch kopmlexer werden dürfte.
Was spricht denn gegen die Hilfsspalte?
Gruß Werner
.. , - ...
Anzeige
AW: nein, MAX() allein hilft nicht ...
01.06.2021 20:14:20
Sandra
Hallo Werner, danke für die Info. Deine Formel funktioniert in meiner Bsp Liste aber in der Original-Datei konnte ich es nicht umsetzen.
ich hab aber die Daten mit der MAX Formel gecheckt und das Ergebnis stimmt..
danke!
LG
AW: bittesschön, doch beachte ...
02.06.2021 09:16:25
neopa
Hallo Sandra,
... es besteht bei der von Dir eingesetzten Hilfsspaltenformel, dann immer die Möglichkeit, dass es für eine "Nr" mit unterschiedlichen Datum den gleichen max. BBA Wert gibt bzw. geben könnte, was dann zu einem überhöhten Ergebniswert führen würde.
Ich rate Dir also davon ab.
Gruß Werner
.. , - ...
Anzeige
;

Forumthreads zu verwandten Themen

Summe ohne doppelte
Benny

Hallo zusammen,
ich habe ein scheinend sehr einfaches Problem, aber ich komme zu keiner Lösung. Ich habe in einer Spalte Zahlen:
1
2
1
3
4
4
Er soll mir nun die Zahlen addieren, jedoch doppelte nur einmal rechnen. Hier sollte also 10 herauskommen und nicht 15.
17.11.2008 14:53:00
Thread-ID: 1025235
4 Beiträge
Variable Gültigkeitsliste ohne doppelte Werte
Susi

Hallo liebe Excel-Freunde,
ich möchte gerne aus einer Datentabelle den Inhalt einer Spalte für eine Auswertung über eine Variable Gültigkeitsliste ziehen.
Zur Verdeutlichung hier ein einfaches Spiel-Beispiel:
Spalte A Spalte B
Äpfel 10
Äpfel 20
Birnen...
28.11.2008 10:08:27
Thread-ID: 1028603
5 Beiträge
kkleinste ohne doppelte werte
gisela

Hallo zusammen,
nach einigen Stunden Recherche benötige ich noch mal Eure Hilfe.
Verschiedene Datumsangaben (siehe Datei) sollen aufsteigend ohne doppelte Werte angezeigt werden.
Meine Formel funktioniert nur bedingt. Irgendetwas stimmt nicht.
Kann mir bitte jemand helfen?
h...
31.01.2009 23:47:11
Thread-ID: 1045792
2 Beiträge
Gültigkeite - Liste ohne doppelte Werte
susi

Hallo,
ich habe eine kleines, eher optisch unschönes Problem. In einer Tabelle wird von den Nutzern auf dem ersten TB eine Liste mit Werte al la ABC1000; ABC1010 ... eingetragen. Nun können sich aber diese Werte auch wiederholen. Auf einem zweiten TB habe ich eine Spalte die sich über DATEN...
13.08.2009 07:57:25
Thread-ID: 1095462
2 Beiträge
Zählen von Zellen ohne doppelte / mehrfache Werte | Herbers Excel-Forum
Julia
Hey Leute,
ich habe eine vorgefertigte Excel-Datei bekommen und muss in einem neuen Tabellenblatt die Anzahl der Daten zählen.
Die Daten befinden sich auf dem Tabellenblatt "VZ-KVs" im Bereich von "Z10S2:Z1009S2" (ist in einer Spalte).
Bei den Daten handelt es sich um Text mit Zahlen u...
08.02.2010 20:29:07
Thread-ID: 1136852
6 Beiträge
Liste ohne doppelte Werte
ing.grohn

Hallo Forum,
mit Daten Gültigkeit kann ich verhindern, daß doppelte Eintragungen vorkommen:
Einstellungen Benutzerdefiniert und dann z.B =UND(ISTZAHL(H1);ZÄHLENWENN($H$1:$H$30;H1)
ich habe hierzu ein Makro aufgezeichnet: With Selection.Validation .Delete .Add Type:=xlValidateCustom...
18.10.2010 19:34:20
Thread-ID: 1182959
4 Beiträge
Anzeige
Summe ohne doppelte
Brüderlein

Hallo liebe Excel-Freunde,
habe unter nachfolgendem Link ein scheinbar einfaches Problem erläutert -
komme selbst einfach nicht drauf.
Vielen Dank für jede Hilfe vorab.
Viele Grüße
https://www.herber.de/bbs/user/81708.htm
05.09.2012 10:35:39
Thread-ID: 1277446
7 Beiträge
CMB ohne doppelte Werte
Heike

Hallo liebes Forum,
wie kann ich 2 CMB ohne doppelte Werte anzeigen lassen? Mit einer CMB klappt es!
Also, ich habe in meiner Userform 2 Frames erstellt mit je einer ComboBox.
In Frame1 wird die CMB1 mit folgendem Code ohne doppelte Einträge angezeigt:
Dim lIndxA As Long...
10.05.2013 23:10:44
Thread-ID: 1312526
8 Beiträge
Anzeige
Rang ohne doppelte werte
Walter
Hallo Excelfreunde
Ich habe eine Tabelle (Torschützen), in Spalt "f" Anzahl der Tore
In Spalte "B" steht der Rang. 1,2,2,2,3,3,3
Es sollte so aussehen 1,2, , , 3 , , , ,4 , , ,
Gibt es hier eine Lösung?
Danke für die Hilfe
GrußWalter
30.06.2014 17:19:51
Thread-ID: 1368257
2 Beiträge
Inhalt einer Spalte zählen ohne doppelte werte
Andre

Hallo Liebe Forum Mitglieder!,
irgendwie stehe ich auf dem Schlauch. Ich habe eine Tabelle
Spalte 1 Zeigt mir die Tage an wann was gearbeitet wurde
Spalte 2 Zeigt mir eine Leistungsposition an
Spalte 3 Zeigt mir die Straße an
nun möchte ich das in Spalte 1 die Tage zusammeng...
10.04.2015 07:10:17
Thread-ID: 1420074
14 Beiträge
spalte ohne doppelte werte darstellen
BADA

Hallo liebe Excel-Forum Besucher,
ich versuche seit geraumer Zeit ein Problem zu lösen. Umzwar würde ich gerne eine Spalte einer Tabelle auf eine andere Stelle kopieren. Es sollen aber alle darin enthaltenen Werte nur einmal vorkommen. Also keine Werte mehr doppelt.
Bsp:
1...
07.08.2015 12:10:01
Thread-ID: 1441022
3 Beiträge
Listbox ohne doppelte Werte
Pauline

Hallo zusammen,
ich habe folgendes Problem:
Ich möchte gerne mit diesem UserForm neue Reports anlegen, die dann in der linken Box erscheinen sollen.

Wenn ich auf "Add new report" gehe, erscheint das folgende UserForm:

Die Daten werden aus einem Datenblatt ausgelesen,...
14.10.2015 09:17:14
Thread-ID: 1452194
2 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Summewenns ohne Duplikate

Schritt-für-Schritt-Anleitung

Um in Excel doppelte Werte bei der Verwendung von SUMMEWENNS auszuschließen, kannst Du die folgende Methode anwenden:

  1. Hilfsspalte erstellen: Füge eine Hilfsspalte ein, die den maximalen Wert für die Spalte BBA ermittelt.

    • Verwende die Formel: 
      =WENNFEHLER(AGGREGAT(14;6;C$2:C$99/(A$2:$A$99=A2)/(B$2:$B$99=B2)/(ZÄHLENWENNS(A$2:A2;A2;B$2:B2;B2)=1);1);"")
    • Diese Formel ermittelt den maximalen BBA-Wert nur für die erste Instanz jeder Kombination aus "Nr" und "Datum".

  2. Summe berechnen: Nutze die SUMMEWENNS-Funktion, um die Werte aus der Hilfsspalte zu summieren.

    • Beispiel: 
      =SUMMEWENNS(D:D;B:B;">=G1";B:B;"<=G2")

  3. Daten sortieren (optional): Um die Berechnung zu erleichtern, kannst Du die Datentabelle nach BBA abwärts sortieren, bevor Du die Formeln anwendest.

Häufige Fehler und Lösungen

  • Problem: Die Formel gibt falsche Summen zurück.

    • Lösung: Überprüfe, ob Du die Hilfsspalte korrekt eingerichtet hast und ob die Bedingungen in der SUMMEWENNS-Formel stimmen.

  • Problem: Echte Duplikate werden nicht korrekt ausgeschlossen.

    • Lösung: Stelle sicher, dass Du die richtigen Spalten in der VERGLEICH-Funktion verwendest, um Duplikate zu identifizieren.

Alternative Methoden

Es gibt verschiedene Möglichkeiten, um in Excel doppelte Werte nur einmal zu summieren:

  1. SUMMENPRODUKT: Nutze SUMMENPRODUKT zusammen mit VERGLEICH, um eine ähnliche Funktionalität wie SUMMEWENNS zu erreichen.

    • Beispiel: 
      =SUMMENPRODUKT(C2:C999; (B2:B999>=G1)*(B2:B999<=G2)*(VERGLEICH(A2:A999&B2:B999&C2:C999;A2:A999&B2:B999&C2:C999;0)=ZEILE(A2:A999)-1))

  2. Datenbankfunktionen: Verwende Datenbankfunktionen wie DBSUM, um eine Summe über eine bestimmte Datenbank zu berechnen, wo Duplikate ausgeschlossen sind.

Praktische Beispiele

Stell Dir vor, Du hast folgende Tabelle:

Nr Datum BBA
7778 01.03.2021 1
7778 01.03.2021 0,3
7778 01.03.2021 1

Um die Summe ohne doppelte Werte zu berechnen, erstellst Du eine Hilfsspalte, die den maximalen BBA-Wert ermittelt und dann mit SUMMEWENNS summierst.

Tipps für Profis

  • Hilfsspalte optimieren: Halte die Hilfsspalte so schlank wie möglich, um die Übersichtlichkeit zu bewahren.
  • Formeln anpassen: Achte darauf, dass Du die Formeln an Deine spezifischen Daten anpasst, insbesondere die Bereiche.
  • Datenvalidierung: Überprüfe Deine Daten regelmäßig, um sicherzustellen, dass keine Duplikate unentdeckt bleiben.

FAQ: Häufige Fragen

1. Wie kann ich doppelte Werte in einer Summe vermeiden? Du kannst dies erreichen, indem Du eine Hilfsspalte erstellst, die den maximalen Wert ermittelt und dann mit SUMMEWENNS summierst.

2. Welche Excel-Version benötige ich für diese Funktionen? Diese Funktionen sind in Excel 2010 und späteren Versionen verfügbar.

3. Gibt es eine Möglichkeit, dies ohne Hilfsspalte zu tun? Es ist möglich, jedoch wird die Formel deutlich komplexer und schwieriger zu handhaben. Es wird empfohlen, die Hilfsspalte zu verwenden.

Beliebteste Forumthreads (12 Monate)

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