AnzahlIstleer mit Bedingung

Bild

Betrifft: AnzahlIstleer mit Bedingung
von: Ralf
Geschrieben am: 23.04.2015 11:39:08

Hallo an die Experten,
ich soll eine Excel Liste für den Stand der Kassenbuchungen für verschiedene Objekte erstellen und dabei habe ich ein Problem.
In Spalte A habe ich die Objekte (Bsp. A-I), in Spalte B trage ich den Monat und das Jahr der abgebenen Kassen ein. Dieser Wert ändert sich also ständig.
In Spalte G bis R habe ich dann die Monate Januar bis Dezember.
Wenn nun eine Kasse in einem Objekt gebucht ist, kommt in die entsprechende Zelle ein "x".
Es soll dann unter der Tabelle eine Gesamtsumme der noch nicht gebuchten Kassen erscheinen. Ich habe bisher mit der Formel "=ANZAHLLEEREZELLEN(G6:R14)" eine Summe fürs ganze Jahr.
Nun die Frage: Wie kann ich die Formel so ändern oder eine andere Formel erstellen, dass Excel die Leerzellen in Abhängigkeit der Spalte B zählt, also unter Berücksichtigung der vorliegenden Kassen.
Für eine Antwort wäre ich sehr dankbar und wünsche allen noch einen schönen und sonnigen Tag.
Eine Musterdatei habe ich hier:
https://www.herber.de/bbs/user/97262.xlsx

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Rudi Maintaire
Geschrieben am: 23.04.2015 11:46:57
Hallo,
also die leeren wenn B nicht leer ist?
=SUMMENPRODUKT((B8:B16<>"")*(G8:R16=""))
Gruß
Rudi

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Ralf
Geschrieben am: 23.04.2015 12:06:05
Hallo Rudi,
danke für Deine Antwort.
Aber es geht nicht darum, ob B leer ist.
Es sollen nur die leeren Spalten gezählt werden, bis zum Monat, der in B steht.
Also wenn ich in B z.B. Feb/15 stehen habe, dann soll nur in G und H die leeren Zellen gezählt werden und wenn ich den Monat ändere also März, April... sollen dann die Leerzellen von Januar bis zu dem Monat berücksichtigt werden, der in B steht.
Ziel ist es, dass ich heute sagen kann, von den vorhandenen Kassen sind xStück noch nicht gebucht.
So, wie ich es oben stehen habe, werden alle leeren Felder von Januar bis Dezember gezählt, es soll aber nur jeweils bis zu dem Monat gezählt werden, der in B steht. Und dieser Wert ändert sich mit jeder abgegebenen Kasse.
Ich hoffe ich konnte es verständlich machen.
Gruß Ralf

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Rudi Maintaire
Geschrieben am: 23.04.2015 12:40:54
Hallo,
da stehen aber unterschiedliche Monate
Gruß
Rudi

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Ralf
Geschrieben am: 23.04.2015 12:45:27
Hallo,
Ja, das ist ja das Problem, da die Kassen nicht einheitlich kommen. Und es ändert sich jeden Monat.
Gruß Ralf

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Daniel
Geschrieben am: 23.04.2015 12:44:27
Hi
ich würde hier die anzahl der Leeren Zellen pro Zeile in einer Hilfsspalte ermitteln und dann diese Hilfsspalte aufsummieren.
die Formel für die erste Zeile der Hilfsspalte ist (Formel dann nach unten ziehen):

=ANZAHLLEEREZELLEN(G8:INDEX(G8:R8;1;VERGLEICH(TEXT(B8;"MMM"&".");$G$7:$R$7;0)))
ich vermute mal, dass eine Formel die die berechnung in einer Zelle durchführt, deutlich komplizierter wird als das einfügen einer Hilfsspalte.
Gruß Daniel

Bild

Betrifft: AW: ohne Leerspalte gehts doch
von: Daniel
Geschrieben am: 23.04.2015 13:02:35
ist doch nicht so kompliziert wie ich gedacht habe:
Probiers mal so:

=SUMMENPRODUKT((G8:R16="")*(SPALTE(G8:R16)<=VERGLEICH(TEXT(B8:B16;"MMM"&".");$7:$7;0)))

Gruß Daniel

Bild

Betrifft: AW: ohne Leerspalte gehts doch
von: Ralf
Geschrieben am: 23.04.2015 13:40:32
Hallo Daniel,
habe deine 2. Lösung erst zu spät gesehen.
Ich habe es probiert, in der Musterdatei funktioniert
es auch sehr gut. Aber in meiner richtigen Datei kommt
#NV. Ich musste die Spalten und Zeilen ändern, da ich
wieder eine geänderte Datei zurückbekommen habe.
Es sind die Zeilen um 2 nach oben gerutscht und der
Block ist F bis Q. Aber das dürfte doch keine Auswirkung
haben!?
Gruß Ralf

Bild

Betrifft: AW: ohne Leerspalte gehts doch
von: Daniel
Geschrieben am: 23.04.2015 13:58:06
naja, du musst die Formel dann schon anpassen.
ich schreibe meine Lösungen immer für die gegebne Beispieldatei.
Wenn die Beispieldatei von deiner Echtdatei abweicht, dann musst du die notwendigen Änderungen selber vornehmen.
kleiner Tip noch:
wenn für einen Bereich noch keine Werte vorliegen und dieser Bereich auch nicht gezählt werden soll, solltest du in die Zeile in Spalte B ein "xxx" eintragen und in der Zeile mit den Datumsüberschriften ebenfalls ein "xxx" und zwar VOR den eigentlichen Monatsüberschriften.
desweiteren fällt auf, dass du in der Tabelle mit unterschiedlichen Jahreszahlen in Spalte B arbeitest.
in der Überschriftenzeile stehen jedoch nur Monate ohne Jahresbezug. Das hat zur folge, dass die Spalte bis zu der die Leeren Zellen gezählt werden sollen, nur auf basis des Monats unabhängig vom Jahr ermittelt werden.
Gruß Daniel

Bild

Betrifft: AW: ohne Leerspalte gehts doch
von: Ralf
Geschrieben am: 23.04.2015 14:06:12
Hallo Daniel,
die Anpassung habe ich gemacht, die Spalten und Zeilen habe ich in meiner Datei geändert, aber sonst nichts angerührt an deiner Formel. Ich habe es in der Beispieldatei auch getan und es hat funktioniert, aber die gleiche Formel in meiner Datei ergibt immer den Fehler #NV. Das ist, was ich nicht verstehe.
Das mit dem Jahr war ein Versehen, es ist alles 2015.
Gruß Ralf

Bild

Betrifft: AW: ohne Leerspalte gehts doch
von: Daniel
Geschrieben am: 23.04.2015 15:37:17
hi
ich kenne weder deine richtige Datei, noch die Formeln die du jetzt verwendest.
wie soll ich dir da sagen was du anders machen musst?
warum hast du überhaupt die Beispieldatei anders gestaltet als deine echte Datei?
Gruß Daniel

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Ralf
Geschrieben am: 23.04.2015 13:18:46
Hallo Daniel,
danke für deine Antwort.
Ich hatte auch schon daran gedacht, aber ich wollte die
Hilfsspalte vermeiden, da ich die Datei an meine Vorgesetzten
jeden Monat senden muss. Aus Erfahrung weiß ich, dass Sie alles
zerschießen und dann kein Ergebnis sehen. Dann ist das Geschrei
wieder groß.
Aber wenn es nichts anderes gibt, dann werde ich nicht drum
herum kommen.
Gruß Ralf

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Sheldon
Geschrieben am: 23.04.2015 12:57:57
Hallo Ralf,
mit dieser Matrixfunktion: =SUMME(($G$7:$R$7<=$B$8:$B$16)*(G8:R16<>"x"))
Also eingeben und dann mit Strg+Shift+Enter abschließen.
Gruß
Sheldon

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Ralf
Geschrieben am: 23.04.2015 13:14:36
Hallo Sheldon,
vielen Dank für deine Antwort.
Ich habe die Matrix eingefügt und auch entsprechend
abgeschlossen. Aber er zeigt mir als Ergebnis 0 an
obwohl es 13 sein müsste (nach meiner eigentlichen
Tabelle).
Woran kann das liegen?
Gruß Ralf

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Sheldon
Geschrieben am: 23.04.2015 13:44:59
Das kann nur daran liegen, dass die Eingabe als Matrixfunktion nicht gelungen ist. Die Formel wird, korrekt abgeschlossen, so angezeigt:
{=SUMME(($G$7:$R$7<=$B$8:$B$16)*(G8:R16<>"x"))}
Also mit geschweifter Klammer vor dem Gleichzeichen und hinter der Formel. Falls Du das nicht siehst, einfach nochmal auf die Zelle klicken, dann F2 drücken und anschließend Strg+Shift+Enter (also alle drei Tasten zur gleichen Zeit, als letzte Enter.)
Gruß
Sheldon

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Sheldon
Geschrieben am: 23.04.2015 13:47:00
Ach ja, du musst noch etwas ändern:
In der Zeile 7 sind die Monatsüberschriften als Text eingegeben. Da muss stattdessen ein richtiges Datum eingetragen werden, also 1.1.2015, 1.2.2015 usw. Die Zellen kannst du dann im Zahlenformat mit MMM formatieren. Dann wird nur das dreibuchstabige Kürzel angezeigt.
Das hatte ich ganz vergessen zu erwähnen!
Gruß
Sheldon

Bild

Betrifft: AW: AnzahlIstleer mit Bedingung
von: Ralf
Geschrieben am: 23.04.2015 13:59:04
Hallo Sheldon,
danke, das hat jetzt geklappt. Die geschweifte Klammer hatte ich drum. Es waren die Monate in der oberen Zeile.
Gruß Ralf

 Bild

Beiträge aus den Excel-Beispielen zum Thema "AnzahlIstleer mit Bedingung"