SUMMEWENN mit Datum und Wort

[max....@gmail.com]

Hi,

ich versuche in Google Sheets eine SUMMEWENN mit 2 Bedingungen zu erstellen.
Eine davon ist ein Datum.

Tabelle:
01.01.2018 / b
02.01.2018 / b

Formel:
=SUMME(WENN((A:A=">=01.01.2018")*(B:B="b");1))

Es schmeißt aber immer einen Fehler. Wenn ich es ohne Datum mache oder das Datum als Text formatiere, dann geht es. Aber dann kann ich natürlich keinen Zeitraum bestimmen...

Ich habe schon alle Formatierungen und Schreibweisen versucht und bin dankbar für einen schlauen Rat.

Ziel: Ich will wissen, wie viel mal "b" in Spalte "B" vorkommt, wenn das Datum nach dem 01.01.2018 liegt.

Ich danke euch!

[HR Ernst]

Ich kenne mich zwar nicht gut mit Excel aus, aber mich verwundern die
beiden "" vor und nach dem Datum. Ich würde es mal so versuchen:
=SUMME(WENN((A:A>=01.01.2018)*(B:B="b");1))

Es ist nur so eine Annahme von mir... Claus Busch ist Profi und wird es
dir genau sagen können...

--
Gruß

H.-R. Ernst

[Claus Busch]

Hallo Max,

Am Fri, 13 Jul 2018 05:24:55 -0700 (PDT) schrieb max....@gmail.com:

> Ziel: Ich will wissen, wie viel mal "b" in Spalte "B" vorkommt, wenn das Datum nach dem 01.01.2018 liegt.

du kannst das Datum nicht als String übergeben.

Probiere:
=SUMMENPRODUKT(--(A1:A100>=DATWERT("01.01.2018"))*(B1:B100="b"))
oder
=SUMMENPRODUKT(--(A1:A100>=DATUM(2018;1;1))*(B1:B100="b"))


Mit freundlichen Grüßen
Claus
--
Windows10
Office 2016

[max....@gmail.com]

Wahnsinn!

Jetzt klappts. Aber 2 Fragen.

1.) Warum kann ich die Spalten nicht komplett angeben. Also nicht A:A, sondern muss eine Wert vergeben? Es ist eine dynamische Tabelle, die automatisch immer weiter wächst, weil sie neue Daten bekommt. Das heißt, ich würde die Abgrenzung auch gern dynamisch gestalten und eben nicht mit einem Zeilenwert einschränken.

2.) Ich habe jetzt versucht noch eine Obergrenze des Datums zu setzen, um eine Spanne an Zeit rauszufiltern - das ging aber nicht wie gedacht. Könntest du evtl. nochmal ganz fix ergänzen, wie das dann aussähe?

Und nur interessehalber: was machen diese "--" nach der ersten öffnenden Klammer?

Auf jeden Fall vielen vielen Dank bis hierhin schon mal!

[max....@gmail.com]

> 1.) Warum kann ich die Spalten nicht komplett angeben. Also nicht A:A, sondern muss eine Wert vergeben? Es ist eine dynamische Tabelle, die automatisch immer weiter wächst, weil sie neue Daten bekommt. Das heißt, ich würde die Abgrenzung auch gern dynamisch gestalten und eben nicht mit einem Zeilenwert einschränken.

Das war einfach nur meine Dummheit - funktioniert natürlich

Aber Frage 2 wäre noch klasse und offen :)

[Claus Busch]

Hallo Max,

Am Fri, 13 Jul 2018 06:33:43 -0700 (PDT) schrieb max....@gmail.com:

> Aber Frage 2 wäre noch klasse und offen :)

probiere es mal so für das ganze Jahr 2018:
=SUMMENPRODUKT((A1:A5000>=DATUM(2018;1;1))*(A1:A5000<=DATUM(2018;12;31))*(B1:B5000="b"))

Deine SUMME(WENN.... ist eine Array-Formel und muss mit STRG+Shift+Enter
abgeschlossen werden. SUMMENPRODUKT braucht diesen Abschluss nicht, ist
aber auch eine Array-Formel. Array-Formeln verlangsamen die Mappe, wenn
damit sehr große Bereiche berechnet werden.

Das doppelte Minus war nicht notwendig. Die SUMMENPRODUKT-Formel kann
man auf 2 Arten schreiben. Korrekt, mit durch Semikolon getrennten
Argumenten. Dann sind die beiden Minuszeichen notwendig, denn die
wandeln den Wahrheitswert des Arguments in einen Zahlenwert. Aus WAHR
wird dann 1 und aus FALSCH wird 0.
Wenn man aber innerhalb der Formel eine Operation durchführt, passiert
diese Wandlung automatisch.

Wegen der Dynamik schlage ich dir vor, dynamische Bereichsnamen zu
verwenden. Diese passen sich immer der aktuellen Tabellengröße an.

[max....@gmail.com]

Klasse auch das klappt. Jetzt bin ich größenwahnsinnig geworden und will noch zwei weitere Berechnungen anstellen.

zusätzlich zu dem Datum und dem Wert steht in Spalte C ein € Wert.
2 Ziele:
1.) Wie oft steht unter den gegeben Bedingungen (Datum und Wort) in Spalte C nicht der Wert "0"
2.) Was ist der summierte Wert unter den gegeben Bedingungen (Datum und Wort) in Spalte C nicht der Wert

Hintergrund, da stehen € Werte. Manchmal 0€, manchmal 10€, 20€ etc...

Ich kann dir gar nicht sagen, wie sehr du meinen Freitag verbessert hast! Noch einmal ein unendliches Danke, dass du dir Zeit genommen hast.

[Claus Busch]

Hallo Max,

Am Fri, 13 Jul 2018 06:33:43 -0700 (PDT) schrieb max....@gmail.com:

> Aber Frage 2 wäre noch klasse und offen :)

vergebe z.B. den Namen "_Datum" (ohne Anführungszeichen. Der Unterstrich
davor ist zur Unterscheidung zur Funktion DATUM) und gebe bei "bezieht
sich auf" ein:
=BEREICH.VERSCHIEBEN(Tabelle1!$A$1;;;ANZAHL(Tabelle1!$A:$A))
Für den Buchstaben in Spalte B dann z.B. den Namen "Kategorie" und als
Formel dann:
=BEREICH.VERSCHIEBEN(_Datum;;1)

Dann kannst du für das ganze Jahr 2018 so das b zählen (hier nun die 2
Versionen der SUMMENPRODUKT-Formel:
=SUMMENPRODUKT(--(_Datum>=DATUM(2018;1;1));--(_Datum<=DATUM(2018;12;31));--(Kategorie="b"))
oder
=SUMMENPRODUKT((_Datum>=DATUM(2018;1;1))*(_Datum<=DATUM(2018;12;31))*(Kategorie="b"))

Ich bevorzuge die obere Schreibweise.

Du kannst aber auch eine Pivot-Tabelle erstellen. Ziehe das Datum in den
Zeilenbereich, die Kategorie in den Spalten- und Wertebereich.
Dann kannst du das Datum nach deinen Wünschen grupperen und/oder
filtern.

[Claus Busch]

Hallo Max,

Am Fri, 13 Jul 2018 07:00:21 -0700 (PDT) schrieb max....@gmail.com:

> zusätzlich zu dem Datum und dem Wert steht in Spalte C ein ? Wert.

> 2 Ziele:
> 1.) Wie oft steht unter den gegeben Bedingungen (Datum und Wort) in Spalte C nicht der Wert "0"
> 2.) Was ist der summierte Wert unter den gegeben Bedingungen (Datum und Wort) in Spalte C nicht der Wert
>

> Hintergrund, da stehen ? Werte. Manchmal 0?, manchmal 10?, 20? etc...

schau mal hier rein:
https://1drv.ms/x/s!AqMiGBK2qniTgc9QJoijPLzVAmp9Lg

Wenn du dir die Mappe herunterlädst, kannst du sehen wie die Formel und
die Namen aufgebaut sind. Die Namen findest du im Namensmanager.

[max....@gmail.com]

Problem: ich musste bei der ersten Datumsschreibweise bleiben, weil ich das Datum gern aus einem anderen Feld im Format "TT.MM.JJJJ" ziehen würde. Bei deinem "JJJJ;M;T" gab es da irgendwie Probleme.

Deswegen habe ich jetzt deine Formel so angepasst für #1:
=SUMMENPRODUKT(($A:$A>=DATWERT($D$2))*($A:$A<=DATWERT($D$3))*($B:$B=D4)*($C:$C<>0))

D.h.
D2: steht das Startdatum
D3: steht das Enddatum
D4: steht das Wort bzw die Kategorie wonach er suchen soll

Das zeigt mir als Ergebnis aber immer "0" an, obwohl er da etwas finden müsste :/
Und die Summierung bei #2 konnte ich leider von deiner Formel nicht auf meine ableiten :(

[max....@gmail.com]

[Claus Busch]

Hallo Max,

Das hat nichts mit der Schreibweise zu tun. Die Funktion DATUM ist so
aufgebaut, dass die Werte in der Reihenfolge J, M und T eingegeben
werden müssen.

Wenn du das Datum in Zellen untergebracht hast, ist das ja ein korrekter
Zahlenwert und DATWERT wird nicht benötigt.
=SUMMENPRODUKT(($A:$A>=$D$2)*($A:$A<=$D$3)*($B:$B=D4)*($C:$C<>0))
Zum Summieren dann:
=SUMMENPRODUKT(($A:$A>=$D$2)*($A:$A<=$D$3)*($B:$B=D4)*$C:$C)

Schau mal in meine Mappe (link in vorheriger Antwort)
Falls du das so nicht zum Laufen bekommst, musst du dich an Google
wenden, denn dies hier ist eine Excel-Gruppe und Excel und Google-Docs
haben doch einige gravierende Unterschiede.

[max....@gmail.com]

Hi Claus,

mit Google Sheets verstehe ich - aber ich glaube eher, es liegt an meiner Unfähigkeit.

Schau mal, ich habe das Sheet jetzt mal öffentlich gemacht:
https://docs.google.com/spreadsheets/d/1F-sSYHawHN7jpYqbOdv1XLYFX9gnPt537GfKjMYtlHY/edit?usp=sharing

Kannst du einen Fehler erkennen?

[max....@gmail.com]

Die Formen scheint auch zu funktionieren, weil ich die Anzahl in der nicht "0" steht, jetzt auch hinbekommen habe. Nur mit der Summierung stimmt etwas nicht. Aber was :S

[max....@gmail.com]

Und wieder einen Schritt weiter - leider kann ich Posts nicht editieren.
Es liegt am Tabellenkopf, da der nicht numerisch ist.
Aber wie kann das dann bei dir funktionieren oder beim Datumsfeld?

Wenn ich "Wert" gegen 10 austausche, rechnet er es wie gewollt zusammen.

[Claus Busch]

Hallo Max,

du musst dann entweder mit deinem Bereich erst in Zeile 2 anfangen oder
dynamische Bereichsnamen erstellen. Wenn du Summieren willst, kannst du
keine Zellen mit Text einschließen.

Lade dir mal hier die nun bearbeitete "test" herunter:
https://1drv.ms/x/s!AqMiGBK2qniTgc9SquHVG5XUgX7pfg

[max....@gmail.com]

Ich glaube wir stehen kurz vorm Ziel.
Das klappt auf jeden Fall - aber nur, wenn es im selben Tabellenblatt ist.
Ich habe die Tabelle in meinem Fall auf einem anderen Tabellenblatt. Eine Verlinkung mit tabellenblatt!_Datum funktioniert nicht :O

[Claus Busch]

Hallo Max,

der Name ist global, bezieht sich also auf die Arbeitsmappe und nicht
auf ein Blatt. Darum musst du an den Namen nichts ändern, auch keinen
Blatttnamen vorstellen.
Gegebenenfalls musst du den Blattnamen vor Start- und Enddatum und
Kategorie setzen, falls diese nicht auf dem Blatt mir der Berechnung
sind.

[max....@gmail.com]

Ok, dann scheint genau das in Spreadsheets nicht zu gehen.
Hat diese Referenzierung einen bestimmten Namen, damit ich danach suchen kann, wie das in Spreadsheets geht?

[Claus Busch]

Hallo Max,

Am Fri, 13 Jul 2018 08:30:14 -0700 (PDT) schrieb max....@gmail.com:

> Ok, dann scheint genau das in Spreadsheets nicht zu gehen.
> Hat diese Referenzierung einen bestimmten Namen, damit ich danach suchen kann, wie das in Spreadsheets geht?

ich wollte gerade die "test" in Google öffnen, aber ich bekomme dort
einen Fehler wegen der Namen. In Google müssen die Bereich fest sein,
dynamische Namen sind dort nicht möglich.
Das heißt, du musst mit Bereichen arbeiten und dann die Über
schriften-Zeile darin ausnehmen, z.B. A2:A1000.

[max....@gmail.com]

Genau das war die Lösung.
Es geht sogar "A2:A" das heißt die ganze Spalte außer die erste Ziele. Das bei allen Argumenten getauscht und es läuft. Vielen Dank nochmal!

[HR Ernst]

Am Fri, 13 Jul 2018 08:17:06 -0700 (PDT) schrieb max....@gmail.com:

Ich finde es "*wunderbar*", wenn du so nach und nach mit immer neuen
Prämissen daherkommst...

--
Gruß

H.-R. Ernst