Excel Summe wenn eine Zahl dort steht, aber nur wenn in der anderen Spalte auch das gleiche steht

2 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

Neue Lösung (bei der alten hab ich ohnehin die {Klammern} vergessen), viel viel kürzer und schneller [in K72]:

{=SUMME(WENN(($A$1:$A$9999=A72)*($B$1:$B$9999=B72)*($C$1:$C$9999<>$C$2:$C$10000);1;0))}

Also: wenn Jahr und Monat mit denen der Vorgabezeile übereinstimmen, wird jeder Tageswechsel gezählt.

Voraussetzung: Beim Monatswechsel MUSS auch ein Tageswechsel stattfinden, sonst ist das Ergebnis um eins vermindert. (liesse sich durch ein gleich kompliziertes Korrekturglied noch kompensieren, hab ich aber jetzt nicht mehr gemacht, da vermutlich irrelevant.

Aber wenn zB ein WinterGewitter am 9.Jan stattfände und am 9.Feb (oder März!) wieder, würde im Jan 0 gezählt!
(Bei Bedarf kümmere ich mich noch um die Korrektur, aber nur bei Rückmeldung!)

Achtung: Dies ist eine Matrixformel!
Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.
Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!


Noch ein Tipp: Wenn Du in der Bearbeitungszeile einen selbständigen Formelteil markierst und F9 drückst, erhältst Du ein(e) Ergebnis{liste}. Allerdings dann die Zelle mit Esc verlassen, Enter würde das gezeigte Ergebnis festschreiben! (wenn versehentlich, dann rückgängig machen!). Kürze dazu Deine Listen aber (ALLE TEILE!) erst mal von ... auf zB ..., damits übersichtlich bleibt! (mindestens 2 Zellen markieren, Strg+h, ... ersetzen durch ...) ---

1

Wenn der Monat nicht doch irgendwo auftaucht, würde ich eine Hilfsspalte verwenden (Sp.H), die bei jedem Monatswechsel eins drauflegt (Tag = Spalte A?) H2 enthalte 1), Formel in H3:
=wenn(A3<A2;H2+1;H2)
damit schaffst Du künstlich Monate, unabhängig vom wirklichen Monat bzw von der Monatszahl im Jahr (zählt nach dem 12. Wechsel einfach weiter zu 13, 14, ...)
Nun kannst Du mit Zählenwenn(H2:Hn;13) bzw Summewenn alle Monatsdaten auslesen.

Willst du die Formel nur am Monatsende sichtbar haben, dann in Zeile2 (!):
=Wenn(H3=H2;"";Zählenwenn(H1:H9999;H3)
das liefert Dir immer in der letzten Zeile eines Monats die Anzahl der Einträge, entsprechend mit Summewenn die Summe der Einträge.
Du musst für Dein Ziel eben die entsprechenden Spalten auswählen und wahrscheinlich weißt Du ja, dass Summewenn/Zählenwenn=Mittelwertwenn ergibt.

Probier das erst mal für den ersten Monat (plus ein Tag) aus und zieh die Formeln am Ende runter, sobald es funktioniert.

Da hab ich in der zweiten Formel ein paar Absolutzeichen vergessen und ausserdem einen falschen Wert übernommen, so sollte es richtig sein (in Zeile2 (!) ):
=Wenn(H3=H2;"";Zählenwenn(H$1:H$9999;H2)

0
@Iamiam

Also ich versuch es nochmal besser zu erklären. Die ersten vier Spalten sind Jahr, Monat, Tag, Stunde, dann kommt eines Spalte wo das Datum im Format TT.MM.JJJJ geschrieben wird. Ansonsten ist es wie oben. Ich hatte es probiert mit einer Wenn-Funktion und mit Zählenwenn, aber er hat dann immer nur die Summe von der kompletten Spalte (hier) H genommen und nicht den Stopp am Monatsenden gemacht...

Ich versuche jetzt nochmal deine Formel. Vielen Dank!

0
@fastregister

Also, das Problem jetzt: Ich möchte ja nicht die Anzahl der Tage zählen, sondern nur die Anzahl der unterschiedlichen Tage, bzw eigentlich nur die Anzahl der Durchschnitte der Tage.

Ich bilde ja in der Spalte "Summe aler Tage" die Summen für die Tage eines Monats, d.h. ich addieren alle Werte aus "Durchschnitt des Tages" auf bis Ende des Monats und das für jeden Monat. Dies zeigt er dann nur in der Spalte des letzten Tages eines Monats an. Soweit so gut. Nun müsste ich ja diese Summe am Ende jeden Monats durch die Anzahl der aufsummierten "Durchschnitte des Tages" teilen, aber genau diese zu zählen ist mein Problem. Denn ich bräuchte eigentlich die Bedingung Zählen wenn ISTZAHL, aber ISTZAHL kann ich nicht in ZÄHLENWENN verschachteln..., denn ich will ja nicht die Tage zählen, sondern die Spalte in derauch "Leerzellen" sind.

Ansich hatte ich auch schon überlegt, es immer durch die Anzahl der Tage zu teilen (also Bezug auf die Zelle in der der letzte Tag des Monats steht), aber für manchen Monate sind nicht alle Tage vertreten, also geht das auch nicht.

Ich hoffe ich konnte mein Problem jetzt noch ein bisschen besser schildern! :)

0
@fastregister

Also, meine Forme sieht jetzt so aus

{=WENN(B2189<>B2188;SUMME(1/ZÄHLENWENN(C$2:C2188;C$2:C2188));"")}

in Spalte B steht der Monat, mit dem Vergleich sorge ich dafür, dass er mir dass dann nur ans Monatsende schreibt. In C sehen die Tage. Für einen Monat mit 31Tagen passt es, dann zählt er auch wie viele Unterschiedliche Tage ich habe, aber nun ist es so, dass er immer oben bei C$2 anfängt und wenn ich nun mittendrin einen Monat mit nur 28 Tagen habe, dann zählt er natürlich bis 31, weil in dem ganzen Bereich natürlich insgesamt 31 verschiedene Tage sind.

Was ich also machen müsste, wäre den Bereich in dem er sucht bei jedem neuen Monat neu anfangen zu lassen.

Ich wüsste allerdings nicht, ob das überhaupt geht. :(

0
@fastregister

"den Bereich in dem er sucht bei jedem neuen Monat neu anfangen zu lassen":
äusserst umständlich ginge auch das (müsste ich aber erst mal tief graben...), aber versuchs mal mit SummewennS und Summe(1/ZählenwennS) und der Hilfsspalte H von oben, dabei die Zusatzbedingung Hₙ=Hₙ₋₝, das müsste alle Daten EINES Monats (ausser seiner ersten Zeile) erfassen (in Hilfsspalten?).
In einer sichtbaren Spalte das dann übernehmen mit der Bedingg in Zeile n:
Wenn(Hₙ<Hₙ₊₝;Übernahme, "")

.
So, und nun ist mir auch die Lösung zu Deiner obigen Forderung
bei jedem neuen Monat neu anfangen wieder eingefallen:

die oberste Zeile des Monats erhältst du mit:

=Vergleich(Hn;H:H;0)

und die unterste mit

=Vergleich((Hn)+1;H:H;0)-1

Den Bereich für die Spalte zB E spannt dann auf:

=indirekt("E"&Vergleich(Hn;H:H;0)&":E"&Vergleich((Hn)+1;H:H;0)-1)

Also zB 1/Zählenwenn für alle positiven Werte im Bereich für den Monat Hn:

{=SUMME(1/Zählenwenn(indirekt("E"&Vergleich(Hn;H:H;0)&":E"&Vergleich((Hn)+1;H:H;0)-1);">0"))}

Hab grad keine Muße, das auch auszuprobieren, ich hoffe, die Trockenübung wird erfolgreich!

sehe grade, dass der Index -1 nicht rüberkommt: ₝ ist eine tiefgestellte 1

1
@Iamiam

Ok, also irgendwie hab ich dich grade verloren :( Ich bin auch selbst blöd, denn ich hätte von Anfang an gleich die Spalten und Zeilen dazuschreiben sollen... wollte nur nicht, dass das so ewig lang wird.

Ich häng mal einen Screenshot an und dann verstehst du vielleicht besser wie genau das bei mir aussieht und wenn du noch Muße hast, dann würde ich mich noch sehr über eine Antwort freuen :3 http://s14.directupload.net/images/140803/m2jg28db.png

Habe Zeilen 2-40 rausgelassen, weil die richtig und für dieses Problem -denke ich- irrelevant sind.

Die Zahl 3 in K73 ist dann das was ich brauche. Der Monat 06 hat hier nur die Tage 28, 29 und 30. Deswegen zählt Excel hier 3, wenn ich dann aber weiter runtergehe funktioniert es wie oben beschrieben bei den weiteren Monaten nicht, weil er dann immer nur 31 zählt.

Danke nochmal für deine Hilfe!!!!

0
@fastregister

Leider nur eine sehr langsame Lösung:
Schreibe in K72 die Formel:
=SUMME((HÄUFIGKEIT( INDIREKT( "C"&MAX((($A:$A&$B:$B=A72*1&(B72-1))*ZEILE($A:$A))+1)& ":C"&MAX(($A:$A&$B:$B=A72*1&B72*1)*ZEILE($A:$A))); INDIREKT( "C"&MAX((($A:$A&$B:$B=A72*1&(B72-1))*ZEILE($A:$A))+1)& ":C"&MAX(($A:$A&$B:$B=A72*1&B72*1)*ZEILE($A:$A))))>0)*1)

Diese Formel funktioniert ohne Hilfsspalte und überall in K (oder sonstwo in Z72) und kann dann nach oben und unten kopiert werden.
Da aber Häufigkeit (Dank an Oubyi für eine frühere AW!) jeden Eintrag gegen jeden Eintrag checkt, dauert das durchrechnen -verstärkt durch indirekt- recht lang.
Möglicherweise wäre eine Hilfsspalte doch schneller gewesen!

Ich versuch morgen noch was schnelleres zu finden, aber heute reichts nicht mehr!

0

Was möchtest Du wissen?