PDA

Vollständige Version anzeigen : obere Zeilen nicht mit berechnen


Dosenkohl
27.06.2015, 19:22
Hallo Formelgemeinde,
ich möchte gern im gelben Bereich aller Werte aus Spalte D einmal aufgezählt bekommen. Allerdings ohne den Tabellenkopf "Test".
Wie muss ich die Formel ab E8 verändern damit die Formelberechnung erst ab D7 beginnt.
Habe schon alles mögliche mit "*Zeile(A1)" ausprobiert.
<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='3' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='60pt'><col width='74,25pt'></colgroup><tr style='background-color:#cacaca'><td>*</td><td align='middle'>D</td><td align='middle'>E</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td colspan='1' rowspan='3' align='middle' >Test</td><td colspan='1' rowspan='3' align='middle' >Formelspalte</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' style='font-size:12px; ' >56</td><td align='right' style='background-color:#FFFF00; ' >56</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='middle' style='font-size:12px; ' >28</td><td align='right' style='background-color:#FFFF00; ' >Test</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='middle' style='font-size:12px; ' >25</td><td align='right' style='background-color:#FFFF00; ' >28</td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='middle' style='font-size:12px; ' >54</td><td align='right' style='background-color:#FFFF00; ' >25</td></tr><tr><td style='background-color:#cacaca' align='middle'>11</td><td align='middle' style='font-size:12px; ' >27</td><td align='right' style='background-color:#FFFF00; ' >54</td></tr><tr><td style='background-color:#cacaca' align='middle'>12</td><td align='middle' style='font-size:12px; ' >27</td><td align='right' style='background-color:#FFFF00; ' >27</td></tr><tr><td style='background-color:#cacaca' align='middle'>13</td><td align='middle' style='font-size:12px; ' >26</td><td align='right' style='background-color:#FFFF00; ' >26</td></tr><tr><td style='background-color:#cacaca' align='middle'>14</td><td align='middle' style='font-size:12px; ' >28</td><td align='right' style='background-color:#FFFF00; ' >98</td></tr><tr><td style='background-color:#cacaca' align='middle'>15</td><td align='middle' style='font-size:12px; ' >98</td><td align='right' style='background-color:#FFFF00; ' >50</td></tr><tr><td style='background-color:#cacaca' align='middle'>16</td><td align='middle' style='font-size:12px; ' >54</td><td align='right' style='background-color:#FFFF00; ' >52</td></tr><tr><td style='background-color:#cacaca' align='middle'>17</td><td align='middle' style='font-size:12px; ' >27</td><td align='right' style='background-color:#FFFF00; ' >48</td></tr><tr><td style='background-color:#cacaca' align='middle'>18</td><td align='middle' style='font-size:12px; ' >50</td><td align='right' style='background-color:#FFFF00; ' >38</td></tr><tr><td style='background-color:#cacaca' align='middle'>19</td><td align='middle' style='font-size:12px; ' >28</td><td align='right' style='background-color:#FFFF00; ' >42</td></tr><tr><td style='background-color:#cacaca' align='middle'>20</td><td align='middle' style='font-size:12px; ' >26</td><td align='right' style='background-color:#FFFF00; ' >40</td></tr><tr><td style='background-color:#cacaca' align='middle'>21</td><td align='middle' style='font-size:12px; ' >52</td><td align='right' style='background-color:#FFFF00; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>22</td><td align='middle' style='font-size:12px; ' >48</td><td align='right' style='background-color:#FFFF00; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>23</td><td align='middle' style='font-size:12px; ' >38</td><td align='right' style='background-color:#FFFF00; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>24</td><td align='middle' style='font-size:12px; ' >42</td><td align='right' style='background-color:#FFFF00; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>25</td><td align='middle' style='font-size:12px; ' >40</td><td align='right' style='background-color:#FFFF00; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>26</td><td align='middle' style='font-size:12px; ' >26</td><td align='right' style='background-color:#FFFF00; ' >*</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Zelle</td><td>Formel</td></tr><tr><td>E7</td><td><Span style='color:#222222'>{=INDEX</Span><Span style='color:#0000DD'>(D<Span style='color:#0000DD'>:</Span>D;MIN</Span><Span style='color:#222222'>(WENN</Span><Span style='color:#0000DD'>(D7<Span style='color:#0000DD'>:</Span>D99<Span style='color:#0000DD'><</Span>>"";ZEILE</Span><Span style='color:#222222'>(7:99)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>}</Span></td></tr><tr><td>E8</td><td><Span style='color:#222222'>{=WENNFEHLER</Span><Span style='color:#0000DD'>(INDEX</Span><Span style='color:#222222'>(D<Span style='color:#222222'>:</Span>D;VERGLEICH</Span><Span style='color:#0000DD'>(1;</Span><Span style='color:#222222'>(ZÄHLENWENN</Span><Span style='color:#0000DD'>(E$7:E7;D$1<Span style='color:#0000DD'>:</Span>D$99)</Span><Span style='color:#222222'>=0)</Span><Span style='color:#0000DD'>*</Span><Span style='color:#222222'>(D$1<Span style='color:#222222'>:</Span>D$99<Span style='color:#222222'><</Span>>"")</Span><Span style='color:#0000DD'>;0)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>;"")</Span><Span style='color:#222222'>}</Span></td></tr><tr><td>E9</td><td><Span style='color:#222222'>{=WENNFEHLER</Span><Span style='color:#0000DD'>(INDEX</Span><Span style='color:#222222'>(D<Span style='color:#222222'>:</Span>D;VERGLEICH</Span><Span style='color:#0000DD'>(1;</Span><Span style='color:#222222'>(ZÄHLENWENN</Span><Span style='color:#0000DD'>(E$7:E8;D$1<Span style='color:#0000DD'>:</Span>D$99)</Span><Span style='color:#222222'>=0)</Span><Span style='color:#0000DD'>*</Span><Span style='color:#222222'>(D$1<Span style='color:#222222'>:</Span>D$99<Span style='color:#222222'><</Span>>"")</Span><Span style='color:#0000DD'>;0)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>;"")</Span><Span style='color:#222222'>}</Span></td></tr><tr><td>E10</td><td><Span style='color:#222222'>{=WENNFEHLER</Span><Span style='color:#0000DD'>(INDEX</Span><Span style='color:#222222'>(D<Span style='color:#222222'>:</Span>D;VERGLEICH</Span><Span style='color:#0000DD'>(1;</Span><Span style='color:#222222'>(ZÄHLENWENN</Span><Span style='color:#0000DD'>(E$7:E9;D$1<Span style='color:#0000DD'>:</Span>D$99)</Span><Span style='color:#222222'>=0)</Span><Span style='color:#0000DD'>*</Span><Span style='color:#222222'>(D$1<Span style='color:#222222'>:</Span>D$99<Span style='color:#222222'><</Span>>"")</Span><Span style='color:#0000DD'>;0)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>;"")</Span><Span style='color:#222222'>}</Span></td></tr></table><table style='font-family:Arial; font-size:8pt; background-color:#FFFFFF'><tr><td style='font-weight:bold'>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span style='text-decoration:underline'>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span style='font-weight:bold; color:#FF0000'>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>

Ich danke für eine Anteilnahme.

HS(V)
28.06.2015, 12:26
Hallo,

Du meinst so was?

=SUMME((wennfehler(1/ZÄHLENWENN(D7:D100,D7:D100),0))*(D7:D100))

Eingeben mit Strg+Umschalt+Eingabetaste.

Hajo_Zi
28.06.2015, 12:57
Hallo Seppel,

<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:126px;" /><col style="width:126px;" /><col style="width:126px;" /><col style="width:126px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td colspan="4" rowspan="1;border-color:#000000; color:#0563C1; border-color:#000000; background-color:#FFFFFF ; text-decoration: underline ; text-align:left; "><a href="HTTP://WWW.excelformeln.de/formeln.html?welcher=194">HTTP://WWW.excelformeln.de/formeln.html?welcher=194</a></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td colspan="1" rowspan="3;border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Test</td><td colspan="1" rowspan="3;border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Formelspalte</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#CACACA ;; text-align:center; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#CACACA ;; text-align:center; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">56</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">56</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">56</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">28</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">28</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">9,33333333333333</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">25</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">25</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">25</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">54</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">54</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">27</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">11</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">27</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">27</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">12</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">27</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">26</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">13</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">26</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">98</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">8,66666666666667</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">28</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">50</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">9,33333333333333</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">15</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">98</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">52</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">98</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">16</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">54</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">48</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">27</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">17</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">27</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">38</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">18</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">50</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">42</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">50</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">19</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">28</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">40</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">9,33333333333333</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">20</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">26</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">8,66666666666667</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">21</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">52</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">52</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">22</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">48</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">48</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">23</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">38</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">38</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">24</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">42</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">42</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">25</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">40</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">40</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">26</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">26</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">8,66666666666667</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">27</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Harry</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width:1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFF66; text-align:center; font-weight:bold; "><td>&nbsp;verbundene Zellen&nbsp;</td></tr><tr><td>D3:G3</td></tr><tr><td>D4:&nbsp;D6</td></tr><tr><td>E4:E6</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>E7</td><td>{=INDEX(D:&nbsp;D;MIN(WENN(D7:&nbsp;D100&lt;&gt;"";ZEILE(A7:A100))))}</td><td>$E$7</td><td>&nbsp;</td></tr><tr><td>E8:E26</td><td>{=WENNFEHLER(INDEX($D$7:$D$100;VERGLEICH(1;(ZÄHLENWENN($E$7:E7;$D$7:$D$100)=0)*( $D$7:$D$100&lt;&gt;"");0));"")}</td><td>$E$8</td><td>&nbsp;</td></tr><tr><td>F7:F26</td><td>=SUMME((WENNFEHLER(1/ZÄHLENWENN($D$7:$D$100;$D$7:$D$100);0))*($D$7:$D$100))</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><b>{} Matrixformel mit Strg+Umschalt+Enter abschließen<br/>Matrixformeln sind durch geschweifte Klammern {} eingeschlossen<br/>Diese Klammern nicht eingeben!!</b><br/><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 19.05 einschl. 64 Bit</td></tr></table><br/>

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

Dosenkohl
29.06.2015, 17:32
Hallo,
ich danke euch für die Hilfe.
Die Formel von Hajo hat mir weitergeholfen. (war genau das was ich wollte)
Spalte F nehme ich an sollte Harrys Formel aufzeigen.

Macht weiter so!!!

HS(V)
29.06.2015, 18:58
Meine Formel wird die Summe aller Werte ohne doppelte.