Doppelte Werte nur einmal zählen, Zeitperiode berücksichtigen

Question

Hallo zusammen!
Seit Stunden tüftle ich nun schon an einer passenden Formel. Hoffe ihr könnt mir helfen.
Ich habe eine Liste mit knapp 17000 Einträgen über mehrere Jahre. Nun möchte ich die Anzahl Einträge für z.B. das Jahr 2012 zählen, wobei die doppelten Einträge beim Zählen nicht berücksichtigt werden sollen. Die Spalte mit dem Datum enthält das volle Datum, also z.B. 17.02.2012; Zelle ist auf Datum formatiert

Ich habe es schon mit folgenden Formeln versucht:
=SUMMENPRODUKT((A2:A17000=JAHR(2012)*(1/ZÄHLENWENN(B2:B17000;B2:B17000))))

=SUMMENPRODUKT((A2:A17000>=DATUM(2012;1;1)*(A2:A17000<=DATUM(2012;12;31)*(1/ZÄHLENWENN(B2:B17000;B2:B17000)))))

=WENN(UND(A2:A17000>=DATUM(2012;1;1);(A2:A17000<=DATUM(2012;12;31));(1/ZÄHLENWENN(B2:B17000;B2:B17000)));0)

Diese Formel
{=SUMME((B2:B17000<>"")/ZÄHLENWENN(B2:B17000;B2:B17000))}
zählt mir korrekt alle Einträge und die doppelten nur einmal! Leider berücksichtigt diese Formel nicht die Zeitperiode, also das Jahr 2012

Ich kriege es einfach nicht hin, dass die Periode (2012) berücksichtigt wird. Hat jemand ein Lösung für mein Problem?

Grüss

Ela

Answer 1

Jahr KDnr "Anzahl Kunden (KDnr)
im 2012"
2012 30255 3
2012 30287
2011 30254
2012 30287
2012 30255
2011 30255
2009 30224
2008 30273
2012 30218

Answer 2

Hallo Ela,

probier Folgendes (Beispiel):

In J1 (Hilfsspalte):

=SUMME(WENN(JAHR(A1:$A$15)=2012;B1;0))

und runterkopieren

anschließend in einer Zelle:

=SUMME((J1:J15<>"")/ZÄHLENWENN(J1:J15;J1:J15))

[sub]Achtung Matrixformel:
die geschwungenen Klammern entfernen, anschließend mit Strg/Shift/Enter gleichzeitig abschließen[/sub]

Die Zellbezüge wären in beiden Formeln noch anzupassen.

Gruß

Paul1

Answer 3

Hi Ela,

wenn es in dem Bereich nur Datumswerte gibt (also keine Texte oder Leerzellen) sollte folgende Formel funktionieren:

{=SUMME(1/ZÄHLENWENN(A1:A17000;A1:A17000)*(JAHR(A1:A17000)=2012)*1)}

CU Aliba

Answer 4

Hallo Paul1

Vielen Dank für die prompte Antwort. Leider kann ich an meiner Datenbasis nix ändern. Diese wird automatisch durch eine Schnittstelle am ERP System gespiessen und überschreibt die Hilfstabelle beim Aktualisieren. Ich bräuchte eine Lösung in einer Formel, da die Berechnung in einem anderen Arbeitsblatt eingegeben wird.

LG, Ela71

Answer 5

Hi Aliba

Vielen Dank für deinen Vorschlag, das Problem mit einer einzigen Formel zu lösen. Genau das brauche ich.

Dein Vorschlag gibt bei mir aber #Wert! zurück. :-( Mach ich was falsch? Habe berücksichtigt, dass es sich um eine Matrix-Formel handelt.

Die Formel müsste aber die eindeutigen Werte aus Spalte B zählen, nicht aus Spalte A, aber nur dann, wenn in Spalte A das Jahr z.B. 2012 mit dem Vorgabekriterium (2012) übereinstimmt, und leere Zeilen soll beim Zählen auch nicht berücksichtigen.

LG, Ela

Answer 6

Hallo Ela,

Ganz verstehe ich das nicht

Wenn die Berechnung ohnehin in einem eigenen Arbeitsblatt erfolgt, könnte auch die Hilfsspalte dort erstellt werden, oder wird das Arbeitsblatt für die Berechnung auch überschrieben?

Gruß

Paul1

Answer 7

Hallo Paul1,

Das Resultat wird in einem 2. Arbeitsblatt (2) berechnet.
Das Arbeitsblatt (1), das mir die Daten aus dem ERP System zieht, bleibt unberührt.
Im Arbeitsblatt (2) gebe ich sämtliche Formeln, ein basierend auf den Daten im Arbeitsblatt (1).
Wo soll ich denn hier auf dem Arbeitsblatt (2) eine Hilfstabelle einfügen? Hier habe ich nur die Resultate wie Summe, Durchschnitt, Anzahl etc., errechnet aus den Daten vom Arbeitsblatt (1).

LG, Ela

Answer 8

Hallo Ela,

Die Spalte in Tabelle 2 kannst Du frei wählen, sagen wir z.B. auch die Spalte J:

=SUMME(WENN(JAHR(Tabelle1!A1:$A$15)=2012;Tabelle1!B1;0))
und natürlich auch runterkopieren

die andere Formel in Tabelle2 bleibt in diesem Fall gleich:

=SUMME((J1:J15<>"")/ZÄHLENWENN(J1:J15;J1:J15))

auch hier müssen die Zellbezüge an die Verhältnisse angepasst werden.

Gruß

Paul1

Answer 9

Hi Paul1

Vielen Dank! Aber ich suche wirklich nach einer Lösung ohne
Hilfsspalte. Es kommen täglich Datensätze hinzu und dann müsste ich
ja täglich manuel die Formel in der Hilfsspalte herunterkopieren. Es soll
aber alles vollautomatisch aktualisiert werden.

LG,

Ela71

Answer 10

Hallo Ela,

Ich akzeptiere natürlich Deinen Wunsch und will Dich zu nichts überreden, trotzdem ist es nicht erforderlich die Hilfsspalte täglich aufs neue runterzukopieren, wenn die Formeln so gestaltet werden, dass beim ersten Mal gleich bis ans Tabellenende (Tastenkombination) runterkopiert kopiert wird.

Hätte Dir gerne eine Lösung ohne Hilfsspalte angeboten, ist mir aber trotz umfangreicher Recherchen im Web nicht gelungen.

Verbleibe mit den besten Wünschen


Gruß

Paul1