PDA

Vollständige Version anzeigen : Summe eines variablen Bereichs mit Bedingung


MichaelBe
08.09.2011, 17:11
Hallo zusammen,

ich zerbreche mir gerade den Kopf darüber wie ich folgendes realisieren kann.
Ich habe eine Zeile mit Werten, die ich addieren möchte, allerdings nicht immer die ganze Zeile sondern abhängig von einem Wert in der Selben Spalte etliche Zeilen darüber.

Beispiel:

In Zeile 2 steht ab Spalte A:
1,2,3,4,5,6,7,8,9,10 usw

In Zeile 10 steht ab Spalte A:
5,6,4,89,2,5,9,3,4,6 usw

Nun möchte ich in einer beliebigen Zelle die Werte aus Zeile 10 addieren solange der Wert in der selben Spalte in Zeile 2 unter einem Grenzwert (nehmen wir 7) liegt.

In dem beispiel würden also die ersten 6 Werte addiert.

geht das mit einer Formel oder muss ich hier schon mit VBA ran?

Erich G.
08.09.2011, 17:22
Hi Michael,
probier mal

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">7</td><td style="text-align:right; ">8</td><td style="text-align:right; ">9</td><td style="text-align:right; ">10</td><td style="text-align:right; ">11</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >&nbsp;</td><td style="text-align:right; ">111</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">4</td><td style="text-align:right; ">89</td><td style="text-align:right; ">2</td><td style="text-align:right; ">5</td><td style="text-align:right; ">9</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">6</td><td style="text-align:right; ">987</td><td >&nbsp;</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B4</td><td >=SUMMEWENN(A2:Z2;"&lt;7";A10:Z10)</td></tr></table></td></tr></table>

MichaelBe
09.09.2011, 08:16
Guten Morgen Ericht. Deine Formel scheint soweit zu funktionieren, allerdings möchte ich anstatt der fixen Zahl (7) die aktuelle KW+1 benutzen.
Meine KW Formel funktioniert einzeln aber in deiner Formel als Bedingung nicht, weißt du warum?

=SUMMEWENN('Blattname'!F9:BP9;"<(KÜRZEN((HEUTE()-WOCHENTAG(HEUTE();2)+11-("1/"&JAHR(HEUTE()+4-WOCHENTAG(HEUTE();2))))/7)+1)";'Blattname'!F55:BP55)

mücke
09.09.2011, 08:41
Moin Michael,

ich bin zwar nicht Erich ...
... aber da die KW berechnet wird, müssen die " (Anführungszeichen) etwas anders gesetzt werden, schau mal hier:
<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Arial; font-size:9,9pt; text-align:left; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >A1</td><td >=SUMMEWENN(Blattname!F9:BP9;"&lt;"&<span style=' color:008000; '>(K&Uuml;RZEN<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(HEUTE<span style=' color:#804000; '>()</span>-WOCHENTAG<span style=' color:#804000; '>(HEUTE<span style=' color:#ff7837; '>()</span>;2)</span>+11-<span style=' color:#804000; '>("1/"&JAHR<span style=' color:#ff7837; '>(HEUTE<span style=' color:#8000ff; '>()</span>+4-WOCHENTAG<span style=' color:#8000ff; '>(HEUTE<span style=' color:#545fa5; '>()</span>;2)</span>)</span>)</span>)</span>/7)</span>+1)</span>;Blattname!F55:BP55)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

MichaelBe
09.09.2011, 08:48
Hallo Dirk und vielen Dank.
Kannst du mir vielleicht noch sagen wann ich die Anführungszeichen so setzen muss und wann anders?

mücke
09.09.2011, 09:03
Moin Michael,

bei ">7" wird die 7 als Text erkannt und ist somit keine wirkliche Zahl mehr.
bei ">"& 7 ist die 7 eine Zahl. Für die 7 kann dann auch eine Formel stehen, siehe Beispiel.
<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >&gt;2+5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >&gt;7</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >A1</td><td >="&gt;2+5"</td></tr><tr><td >A2</td><td >="&gt;"&(2+5)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

Erich G.
09.09.2011, 09:05
Hi Michael,
bei der SUMMEWENN-Bedingung dürfen die Gänsefüße nicht um das KÜRZEN(...) usw. herum,
sonst wird die KW nicht berechnet, stattdessen der Formeltext vergleichen.

Darunter noch ein paar Alternativen für die Berechnung der KW+1:

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#99cc00; text-align:right; ">0,00001</td><td style="background-color:#99cc00; text-align:right; ">0,0001</td><td style="background-color:#99cc00; text-align:right; ">0,001</td><td style="background-color:#99cc00; text-align:right; ">0,01</td><td style="background-color:#99cc00; text-align:right; ">0,1</td><td style="background-color:#99cc00; text-align:right; ">1</td><td style="text-align:right; ">10</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#99cc00; text-align:right; ">1</td><td style="background-color:#99cc00; text-align:right; ">2</td><td style="background-color:#99cc00; text-align:right; ">3</td><td style="background-color:#99cc00; text-align:right; ">4</td><td style="background-color:#99cc00; text-align:right; ">35</td><td style="background-color:#99cc00; text-align:right; ">36</td><td style="text-align:right; ">37</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >lang:</td><td style="text-align:right; ">1,11111</td><td >&nbsp;</td><td >&nbsp;</td><td >k&uuml;rzer:</td><td style="text-align:right; ">1,11111</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">37</td><td style="text-align:right; ">37</td><td style="text-align:right; ">37</td><td style="text-align:right; ">37</td><td style="text-align:right; ">37</td><td style="text-align:right; ">37</td><td >&nbsp;</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >G11</td><td >=SUMMEWENN(Blattname!F9:BP9;"&lt;"&<span style=' color:008000; '>(K&Uuml;RZEN<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(HEUTE<span style=' color:#804000; '>()</span>-WOCHENTAG<span style=' color:#804000; '>(HEUTE<span style=' color:#ff7837; '>()</span>;2)</span>+11-<span style=' color:#804000; '>("1/"&JAHR<span style=' color:#ff7837; '>(HEUTE<span style=' color:#8000ff; '>()</span>+4-WOCHENTAG<span style=' color:#8000ff; '>(HEUTE<span style=' color:#545fa5; '>()</span>;2)</span>)</span>)</span>)</span>/7)</span>+1)</span>;Blattname!F5:BP5)</td></tr><tr><td >K11</td><td >=SUMMEWENN(Blattname!F9:BP9;"&lt;"&DM<span style=' color:008000; '>(<span style=' color:#0000ff; '>(HEUTE<span style=' color:#ff0000; '>()</span>+9)</span>/7;)</span>-DM<span style=' color:008000; '>(<span style=' color:#0000ff; '>(6&-1&-JAHR<span style=' color:#ff0000; '>(7*DM<span style=' color:#804000; '>(<span style=' color:#ff7837; '>(HEUTE<span style=' color:#8000ff; '>()</span>+2)</span>/7;)</span>-2)</span>)</span>/7;)</span>+1;Blattname!F5:BP5)</td></tr><tr><td >F13</td><td >=KALENDERWOCHE(HEUTE<span style=' color:008000; '>()</span>;21)+1</td></tr><tr><td >G13</td><td >=DM(<span style=' color:008000; '>(HEUTE<span style=' color:#0000ff; '>()</span>+9)</span>/7;)-DM(<span style=' color:008000; '>(6&-1&-JAHR<span style=' color:#0000ff; '>(7*DM<span style=' color:#ff0000; '>(<span style=' color:#804000; '>(HEUTE<span style=' color:#ff7837; '>()</span>+2)</span>/7;)</span>-2)</span>)</span>/7;)+1</td></tr><tr><td >H13</td><td >=DM(<span style=' color:008000; '>(HEUTE<span style=' color:#0000ff; '>()</span>-<span style=' color:#0000ff; '>(1&-JAHR<span style=' color:#ff0000; '>(HEUTE<span style=' color:#804000; '>()</span>+3-REST<span style=' color:#804000; '>(HEUTE<span style=' color:#ff7837; '>()</span>-2;7)</span>)</span>)</span>-REST<span style=' color:#0000ff; '>(HEUTE<span style=' color:#ff0000; '>()</span>-2;7)</span>)</span>/7;)+2</td></tr><tr><td >I13</td><td >=K&Uuml;RZEN(<span style=' color:008000; '>(HEUTE<span style=' color:#0000ff; '>()</span>-DATUM<span style=' color:#0000ff; '>(JAHR<span style=' color:#ff0000; '>(HEUTE<span style=' color:#804000; '>()</span>+3-REST<span style=' color:#804000; '>(HEUTE<span style=' color:#ff7837; '>()</span>-2;7)</span>)</span>;1;REST<span style=' color:#ff0000; '>(HEUTE<span style=' color:#804000; '>()</span>-2;7)</span>-9)</span>)</span>/7)+1</td></tr><tr><td >J13</td><td >=K&Uuml;RZEN(<span style=' color:008000; '>(HEUTE<span style=' color:#0000ff; '>()</span>-WOCHENTAG<span style=' color:#0000ff; '>(HEUTE<span style=' color:#ff0000; '>()</span>;2)</span>-DATUM<span style=' color:#0000ff; '>(JAHR<span style=' color:#ff0000; '>(HEUTE<span style=' color:#804000; '>()</span>+4-WOCHENTAG<span style=' color:#804000; '>(HEUTE<span style=' color:#ff7837; '>()</span>;2)</span>)</span>;1;-10)</span>)</span>/7)+1</td></tr><tr><td >K13</td><td >=K&Uuml;RZEN(<span style=' color:008000; '>(HEUTE<span style=' color:#0000ff; '>()</span>-WOCHENTAG<span style=' color:#0000ff; '>(HEUTE<span style=' color:#ff0000; '>()</span>;2)</span>+11-<span style=' color:#0000ff; '>("1/"&JAHR<span style=' color:#ff0000; '>(HEUTE<span style=' color:#804000; '>()</span>+4-WOCHENTAG<span style=' color:#804000; '>(HEUTE<span style=' color:#ff7837; '>()</span>;2)</span>)</span>)</span>)</span>/7)+1</td></tr></table></td></tr></table>
(F13 gibt es erst ab Excel2010, evtl. ab 2007)

MichaelBe
09.09.2011, 09:26
Danke für eure ausführlichen Antworten.

Erich, F13 recht bei 2007 noch nach dem Ami System, deswegen verwende ich die lange Formel um die deutschen KWs zu berechnen.