Office: Summenprodukt / Zählenwenn mit UND und ODER

Grüsst euch.
Ich versuche schon seit längerem eine Lösung für mein Problem zu finden, bin aber immer kläglich gescheitert. Hoffentlich kann mir jemand helfen.

Im Anhang ist ein Excel, damit sollte die Beschreibung besser verständlich sein. Ich versuche es mal:
Gelb (Spalte i): da müsste die gesuchte Formel stehen. In Spalte J habe ich eingetragen was das Resultat sein sollte.

Ich habe ein "Datum 2" (Spalte Q). Wenn dieses grösser ist als "Datum 1" (Spalte G) soll folgendes gemacht werden:

Alle Zeilen zählen, bei denen in der Spalte N-P mindestens eine Übereinstimmung mit den Zutaten A-B (Zellen C3-C5, bzw. C8-C10 etc.) auftritt.

Ich hoffe das ist zusammen mit dem Excel verständlich. Ich habe es zuerst mit Zählenwenn probiert, dann schien mir Summenprodukt erfolgsversprechender, aber ich scheitere daran, dass in der Spalte N-P pro Zeile mal ein Treffer auftritt, dann wieder 2 etc. Die Zeile soll aber trotzdem nur 1x gezählt werden.

Ach ja, da ich das Excel auf dem Handy verwende, muss die Lösung leider ohne VBA auskommen.

:)

 

Hallo P...,

Ich nehme an, dass du die Zutatenliste (N:Q) nach der Erstellung der Zielwerte noch gekürzt hast. Deine Beschreibung passt nicht zu den Zielwerten.

Eine mögliche Lösung ist:

=WENN(C3"";SUMMENPRODUKT(($Q$3:$Q$9>G3)*((($N$3:$N$9=C3)+($O$3:$O$9=C3)+($P$3:$P$9=C3))>0));"")

Im Summenprodukt werden die Bedingungen
bei UND multipliziert und
bei ODER addiert und die Summe auf >0 abgefragt.

 

Hallo Oge
Danke für deine Antwort. Die Zielwerte sollten stimmen, dann liegts wohl an der Problembeschreibung *wink.gif*
Habe grad kaum Zeit, ich versuche es später nochmal besser zu präzisieren.
Wenn ich deine Formel in I3 eingebe ist das Resultat 3.
Es werden die Zeilen 6, 8 und 9 gezählt. Das ist so weit richtig. Es sollten aber auch noch die Zeilen 4&5 gezählt werden, weil da Kohle oder Steine vorkommt (wie in C4 und C5).

 

Hallo P...,

ein zweiter Versuch mit ein wenig mehr ODER für die erste Zeile eines Blockes.

=WENN(C3"";SUMMENPRODUKT(($Q$3:$Q$9>G3)*((($N$3:$N$9=C3)+($O$3:$O$9=C3)+($P$3:$P$9=C3)+($N$3:$N$9=C4)+($O$3:$O$9=C4) +($P$3:$P$9=C4)+($N$3:$N$9=C5)+($O$3:$O$9=C5)+($P$3:$P$9=C5))>0));"")

 

Wow, das sieht sehr gut aus! Tatsächlich hatte ich beim letzten Block einen falschen Zielwert angegeben, aber deine Formel liefert das korrekte Resultat.
Ich habe noch nicht ganz verstanden warum deine Formel funktioniert und warum meine sehr ähnlichen Versuche gescheitert sind. Am Montag werde ich das noch genauer anschauen. Aber schonmal herzlichen Dank, du hast mir sehr geholfen! Hatte schon fast befürchtet, dass die Aufgabe nicht lösbar sei.
Aber scheinbar hatte ich einfach ein Durcheinander, vermutlich lags an falschen Klammern.

Edit: Aaaaah! Die Abfrage der Summe auf >0 ist der Trick. Sehr schlau, oder zumindest viel schlauer als meine Versuche. Nochmals herzlichen Dank!

 

Also die Antwort von Oge war genau das, was ich gesucht hatte. Jetzt stellt sich mir aber ein neues Problem. Scheinbar ist Summenprodukt eine relativ langsame Funktion. Mein Originalfile ist aber sehr gross und Excel stösst zumindest bei mir auf dem Handy langsam an seine Grenzen.
Hat jemand vielleicht eine Idee, wie man das schlanker (schneller) lösen könnte?

Das Excel habe ich nochmals angehängt, den Fehler ausgemerzt. In den gelben Feldern steht die Formel von Oge, für die ich einen Ersatz suche.