Herbers Excel-Forum - das Archiv

Summe aus Texten+Zahl mit Bedingung


Betrifft: Summe aus Texten+Zahl mit Bedingung
von: Bernd

Geschrieben am: 20.09.2019 11:11:01
Hallo zusammen,
vielleicht kann mir hier wer helfen.
vorab noch… die Inhalte bedeuten A = Arzt, ZA = für Zeitausgleich - dahinter dann die jeweilige Zeitdauer.
Nun sollen aber aus diesen 'Texten' die Summe der Zeit ermittelt werden (also Summe alles hinter A oder ZA) und das eben noch mit der Möglichkeit der Datumseingrenzung (blaue Eingabefelder)
Jemand nen Ansatz, wie man das auch ohne Hilffspalte ermitteln kann.
 ABCDE
101.01.2019  Datumsbegrenzung 
202.01.2019A0,5 vonbis
303.01.2019  01.01.201912.01.2019
404.01.2019    
505.01.2019ZA1,5 Summe A: 
606.01.2019  Summe ZA: 
707.01.2019A1,5   
808.01.2019    
909.01.2019    
1010.01.2019ZA0,25   
1111.01.2019    
1212.01.2019    
1313.01.2019    
1414.01.2019    
1515.01.2019    
1616.01.2019    
1717.01.2019ZA6,5   
1818.01.2019    
1919.01.2019    
2020.01.2019    
2121.01.2019    
2222.01.2019    
2323.01.2019    
2424.01.2019ZA3,75   
2525.01.2019    
2626.01.2019A3,5   
2727.01.2019    

Danke Euch, Gruß Bernd

Betrifft: AW: Summe aus Texten+Zahl mit Bedingung
von: 1714063.html
Geschrieben am: 20.09.2019 11:13:21
Hallo Bernd,
Hilfaspalte und Zahl abtrennen.
http://www.excelformeln.de/formeln.html?welcher=27

Betrifft: AW: Summe aus Texten+Zahl mit Bedingung
von: 1714065.html
Geschrieben am: 20.09.2019 11:43:18
Hallo Bernd,
bescheidene Frage: Warum werden die Zeiten und der Grund nicht in zwei Spalten erfasst? Excel hat genug davon.
Gruß Werner

Betrifft: AW: Summe aus Texten+Zahl mit Bedingung
von: 1714073.html

Geschrieben am: 20.09.2019 12:19:09
Hi,
vorab vielen Dank Euch.
Lösung mit Hilfsspalte scheidet aus.
Ok, werd ein bisschen konkreter...
im konkreten Fall geht es geht um eine Kalenderübersicht - das Datum steht in den Spalten und beinhaltet ist ein Jahr.
In den Zeilen dann zig Persnummern und eingetragen werdem im Prinzip nur ganztägige Ereignisse wie Urlaub, Schule, Montage, Krank mit einem Kennzeichen mit Ausnahme von Arztgang und Zeitausgleich, welche halt bisher mit A und ZA beginnend + Zeitdauer eingetragen werden.
Eine Summe wird da bisher erzeugt mit zig Zählenwennformeln - so dass der Bereich von 0,25-8 abgedeckt ist.
Nun kam eben noch die Anforderung auf diese Tage bzw Zeiten nur über eine gewisse Zeitspanne zu ermitteln. bei den ganztägige ists problemlos, zählenwenns und es paßt.
bei den zwei kombinierten Inhalten dagegen komm ich so nicht weiter und deshalb eben die Frage hier.
Der komplette Kalender verwirrt denk eher, deshalb das einfache und abgewandelte Beispiel.
Vielleicht doch noch wer ne andere Idee?
Gruß Bernd

Betrifft: AW: Summe aus Texten+Zahl mit Bedingung
von: 1714078.html
Geschrieben am: 20.09.2019 12:53:32
Warum begreifen wir Helfer eigentlich nicht, dass Hilfsspalten per se etwas Böses sind?
(PQ wahrscheinlich auch...)
Gruß
Günther

Betrifft: AW: Summe aus Texten+Zahl mit Bedingung
von: 1714082.html
Geschrieben am: 20.09.2019 13:21:56
Hallöle,
ja wäre prinzipell auch kein Problem.
In meinem Fall brauch ich dann aber pro PersNr. eine Hilfszeile, sind dann ca. 30 oder 40, daher etwas unkonfortabel. :)
Daher wär mir ne andere Lösung lieber.
Gruß Bernd

Betrifft: mit 2 Arrayformeln
von: 1714086.html
Geschrieben am: 20.09.2019 13:28:11
Hi,
{=SUMME(WENN(LINKS(B1:B30)="A";TEIL(B1:B30;2;9)*1))}
und
{=SUMME(WENN(LINKS(B1:B30;2)="ZA";TEIL(B1:B30;3;9)*1))}
Die Datumseingrenzung bekommst Du selber hin.
WF

Betrifft: AW: Summe aus Texten+Zahl mit Bedingung
von: 1714087.html
Geschrieben am: 20.09.2019 13:31:26
Bernd,
du brauchst im schlimmsten Fall 2 Hilfs-SPALTEN. Eine, wo A, AB oder "" (berechnet) drin steht und eine mit der Zeit. Und dann mit SUMMEWENN() berechnen.
Gruß
Günther

Betrifft: AW: Summe aus Texten+Zahl mit Bedingung
von: 1714102.html

Geschrieben am: 20.09.2019 14:54:08
Hi
die Datumseinschränkung würde ich hier, da es sich um einen linearen Kalender handelt, nicht über zusätzliche Bedingungen im ZählenWenns oder SummenProdukt machen, sondern dadurch dass ich die Zellbereiche über Index und Vergleich entsprechend einschränke.
Beispiel Summe ZA ohne Einschränkung (Achtung Matrixformel)
{=Summe(Wenn(Links(B1:B27;2)="ZA";Wert(Teil(B1:B27;3;9));0))}
mit Datumseinschränkung ersetzt man dann den Zellbezug B1:B27 durch Index(B:B;Vergleich(D3;A:A;1)):Index(B:B;Vergleich(E3;A:A;1))
{=Summe(Wenn(Links(Index(B:B;Vergleich(D3;A:A;1)):Index(B:B;Vergleich(E3;A:A;1));2)="ZA"; Wert(Teil(Index(B:B;Vergleich(D3;A:A;1)):Index(B:B;Vergleich(E3;A:A;1));3;9));0)) }

das macht die Formel zwar deutlich länger, reduziert aber die Rechenlast im System, weil die Schleife der Matrix dann nur noch über die tatsächlich benötigten Zeilen läuft und nicht mehr über den kompletten Kalender.
Zum kürzen der Formel kann man die beiden Vergleiche zum Berechnen der Start- und Endzeile noch in zwei Hilfszellen auslagern. Da dann nur zwei Zellen benötigt werden, sollte das problemlos möglich sein.
Gruß Daniel

Betrifft: AW: Summe aus Texten+Zahl mit Bedingung
von: 1714108.html

Geschrieben am: 20.09.2019 15:32:18
Hallo,
vielen Dank Euch allen :), habs mal versucht auf meine Bereiche anzupassen.
ohne Datumseinschränkung funktionierts wunderbar.
Mit bisher noch nicht wirklich, und ganz ehrlich weiss ich nicht genau woran das liegt... aber ich versuch mich mal übers Wochenende daran.
Euch ein schönes Wochenende, danke nochmals Bernd

Betrifft: AW: Summe aus Texten+Zahl mit Bedingung
von: 1714109.html

Geschrieben am: 20.09.2019 15:32:25
Hallo,
vielen Dank Euch allen :), habs mal versucht auf meine Bereiche anzupassen.
ohne Datumseinschränkung funktionierts wunderbar.
Mit bisher noch nicht wirklich, und ganz ehrlich weiss ich nicht genau woran das liegt... aber ich versuch mich mal übers Wochenende daran.
Euch ein schönes Wochenende, danke nochmals Bernd

Betrifft: AW: Summe aus Texten+Zahl mit Bedingung
von: 1714118.html
Geschrieben am: 20.09.2019 15:46:42
Hi
also bei mir geht's auch mit Datumseinschränkung, mit genau dieser Formel in Kombination mit deiner Beispieltabelle.
Bedacht, dass das ebenfalls eine Matrixformel (Eingabe mit STRG+SHIFT+ENTER) ist?
Gruß Daniel

Betrifft: mit Datumseinschränkung ist doch einfach ?
von: 1714122.html
Geschrieben am: 20.09.2019 16:00:48
Hi,
{=SUMME(WENN((LINKS(B1:B30)="A")*(A1:A30>=D3)*(A1:A30<=E3);TEIL(B1:B30;2;9)*1))}
und
{=SUMME(WENN((LINKS(B1:B30;2)="ZA")*(A1:A30>=D3)*(A1:A30<=E3);TEIL(B1:B30;3;9)*1))}
WF

Excel-Beispiele zum Thema "Summe aus Texten+Zahl mit Bedingung"
Wert aus Spalte C, wenn Spalten A und B Bedingungen erfüllen Beim Erfüllen einer Bedingungen Farbe für 1 Minute
Anzahl von Werten nach verschiedenen Bedingungen. Zeilen zählen, in denen zwei Bedingungen zutreffen
Dem Zellkontextmenü ein Menü "Zahlungsbedingungen" hinzufügen Wert eintragen, wenn Bedingte-Formatierung-Bedingung zutrifft
Zelle markieren, wenn zwei Bedingungen erfüllt sind
Bewerten Sie hier bitte das Excel-Portal