PDA

Vollständige Version anzeigen : Hürde Excel meistern - Formelfrage(n)


Micha890
17.09.2011, 00:15
Hallo Excel-Profis ;-)

Ich stehe vor einem riesen Problem und komme einfach nicht weiter.
Excel will nicht so wie ich es will :(

Ich habe 3 Formeln, welche ich anpassen muss, damit die Werte korrekt angezeigt werden.

Und zwar seht ihr unten einen Beispielauszug, welchen ich schnell erstellt habe um Euch mein Vorhaben besser erklären zu können.

Ihr seht oberhalb unter Spalte A die "Anzahl", daneben den 1. Wert, 2. Wert und die Summe(Ges-Wert) aus Wert1+2. Wert4 ist eine Info für mich, welche Wert1+2 nochmals direkt nebeneinander schreibt. Dann folgen die Optionen1-5. Soweit so gut.
Jetzt möchte ich, dass der Ges-Wert, Wert4 und die Optionen automatisch gesucht, gezählt und multipliziert werden. Diese Ergebnisse sollen unter der roten Zeile angezeigt werden.

Beispiel: Excel soll zählen, wie oft die z.B. 6020 etc. unter Wert4 steht.
Dafür muss Excel zum einen die komplette Spalte E durchsuchen, die
6020 finden, jeweils die Anzahl beachten(!!!) und dann alles zusammengerechnet unter der roten Zeile bei 6020 anzeigen.

Dasselbe soll es mit den anderen Werten machen, ebenfalls mit dem Ges-Wert und den jeweiligen Optionen.

Dies ist nun meine Hürde, welche ich nicht einfach nicht überwinden kann.
Ich schaffe es nicht, Excel zu sagen, dass es, wenn es einen Wert findet, die Anzahl beachtet und diese quasi im Kopf behält, um später alles zusammengerechnet anzuzeigen.

Ich hoffe ich verwirre niemanden :p

Könnt ihr mir helfen?? Also meine ausgearbeitete Liste ist recht komplex, es geht um viel mehr als um das unten abgebildete, nur bekomme ich diese eine bzw. 3 Formeln mit aller Liebe nicht zustande.

Ich danke vielmals im Voraus ;-)

http://s1.directupload.net/images/110917/bwehiui5.jpg (http://www.directupload.net)

Hajo_Zi
17.09.2011, 07:55
ich benutze schon Version 2010, aber selbst diese Vesion kann aus einem Bild keine Tabelle erstellen. Warum sollte ich die Zeit in die Erstellung stecken, das hast Du doch schon?

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm" onclick="window.open(this.href);return false"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

josef e
17.09.2011, 09:06
<div style="width:85%; margin-left:5px; margin-right:15px; text-align:justify;">
Hallo Micha,

<b>Tabelle2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Tahoma,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;" /></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></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="color:#ff0000; font-weight:bold; text-align:center; ">Anzahl</td><td style="font-weight:bold; text-align:center; ">Wert1</td><td style="font-weight:bold; text-align:center; ">Wert2</td><td style="background-color:#99ccff; font-weight:bold; text-align:center; ">Ges-Wert</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Wert4</td><td style="background-color:#99cc00; font-weight:bold; text-align:center; ">Option1</td><td style="background-color:#99cc00; font-weight:bold; text-align:center; ">Option2</td><td style="background-color:#99cc00; font-weight:bold; text-align:center; ">Option3</td><td style="background-color:#99cc00; font-weight:bold; text-align:center; ">Option4</td><td style="background-color:#99cc00; font-weight:bold; text-align:center; ">Option5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#ff0000; font-weight:bold; text-align:center; ">1</td><td style="text-align:center; ">30</td><td style="text-align:center; ">30</td><td style="background-color:#99ccff; font-weight:bold; text-align:center; ">60</td><td style="background-color:#ffff00; text-align:center; ">3030</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; ">&nbsp;</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; ">&nbsp;</td><td style="background-color:#99cc00; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#ff0000; font-weight:bold; text-align:center; ">2</td><td style="text-align:center; ">60</td><td style="text-align:center; ">20</td><td style="background-color:#99ccff; font-weight:bold; text-align:center; ">80</td><td style="background-color:#ffff00; text-align:center; ">6020</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; ">&nbsp;</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#ff0000; font-weight:bold; text-align:center; ">3</td><td style="text-align:center; ">50</td><td style="text-align:center; ">70</td><td style="background-color:#99ccff; font-weight:bold; text-align:center; ">120</td><td style="background-color:#ffff00; text-align:center; ">5070</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; ">&nbsp;</td><td style="background-color:#99cc00; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#ff0000; font-weight:bold; text-align:center; ">4</td><td style="text-align:center; ">60</td><td style="text-align:center; ">20</td><td style="background-color:#99ccff; font-weight:bold; text-align:center; ">80</td><td style="background-color:#ffff00; text-align:center; ">6020</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; ">&nbsp;</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; text-align:center; ">x</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="color:#ff0000; font-weight:bold; text-align:center; ">5</td><td style="text-align:center; ">70</td><td style="text-align:center; ">30</td><td style="background-color:#99ccff; font-weight:bold; text-align:center; ">100</td><td style="background-color:#ffff00; text-align:center; ">7030</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; text-align:center; ">x</td><td style="background-color:#99cc00; text-align:center; ">x</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><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><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:#ffff00; text-align:center; ">3030</td><td style="background-color:#ffff00; text-align:right; ">1</td><td >&nbsp;</td><td style="background-color:#99cc00; ">Option1</td><td style="background-color:#99cc00; text-align:right; ">15</td><td >&nbsp;</td><td style="background-color:#99ccff; text-align:right; ">0</td><td style="background-color:#99ccff; text-align:right; ">70</td><td style="background-color:#99ccff; text-align:right; ">1</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffff00; text-align:center; ">4050</td><td style="background-color:#ffff00; text-align:right; ">0</td><td >&nbsp;</td><td style="background-color:#99cc00; ">Option2</td><td style="background-color:#99cc00; text-align:right; ">10</td><td >&nbsp;</td><td style="background-color:#99ccff; text-align:right; ">71</td><td style="background-color:#99ccff; text-align:right; ">100</td><td style="background-color:#99ccff; text-align:right; ">11</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffff00; text-align:center; ">5070</td><td style="background-color:#ffff00; text-align:right; ">3</td><td >&nbsp;</td><td style="background-color:#99cc00; ">Option3</td><td style="background-color:#99cc00; text-align:right; ">13</td><td >&nbsp;</td><td style="background-color:#99ccff; text-align:right; ">101</td><td style="background-color:#99ccff; text-align:right; ">150</td><td style="background-color:#99ccff; text-align:right; ">3</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffff00; text-align:center; ">6020</td><td style="background-color:#ffff00; text-align:right; ">6</td><td >&nbsp;</td><td style="background-color:#99cc00; ">Option4</td><td style="background-color:#99cc00; text-align:right; ">11</td><td >&nbsp;</td><td style="background-color:#99ccff; text-align:right; ">151</td><td style="background-color:#99ccff; text-align:right; ">200</td><td style="background-color:#99ccff; text-align:right; ">0</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffff00; text-align:center; ">5040</td><td style="background-color:#ffff00; text-align:right; ">0</td><td >&nbsp;</td><td style="background-color:#99cc00; ">Option5</td><td style="background-color:#99cc00; text-align:right; ">9</td><td >&nbsp;</td><td style="background-color:#99ccff; text-align:right; ">201</td><td style="background-color:#99ccff; text-align:right; ">230</td><td style="background-color:#99ccff; text-align:right; ">0</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffff00; text-align:center; ">7030</td><td style="background-color:#ffff00; text-align:right; ">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></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#ffff00; text-align:center; ">8070</td><td style="background-color:#ffff00; text-align:right; ">0</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; " >16</td><td style="font-family:Arial; font-size:9pt; ">&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></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 >D2</td><td >=B2+C2</td></tr><tr><td >E2</td><td >=(B2&C2)*1</td></tr><tr><td >B9</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($E$2:$E$6=A9)</span>*$A$2:$A$6)</td></tr><tr><td >E9</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($F$1:$J$1=D9)</span>*<span style=' color:008000; '>($F$2:$J$6="x")</span>*$A$2:$A$6)</td></tr><tr><td >I9</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($D$2:$D$6&gt;=G9)</span>*<span style=' color:008000; '>($D$2:$D$6&lt;=H9)</span>*$A$2:$A$6)</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>




</div>

<div style="width:100px; text-align:center; color:white; font-style:italic; font-size:0.8em; font-family:Tahoma; background-color:royalblue;margin-left:5px; margin-top:15px; padding:4px; border:3px double darkblue;">&laquo; Gru&szlig; Sepp &raquo;</div>

Micha890
17.09.2011, 11:36
Hallo!
@ Hajo: Sorry, ich wusste es derzeit nicht besser, wie ich Euch diese Tabelle beibringen/darstellen kann. Den Tipp von Josef mit dem Marko? Jeanie habe ich versucht, jedoch bekomme ich dies mit meiner 2003er Version nicht zum Laufen.

Soooo, und dann @Josef:
Tausend Dank !!! Die Formeln funktionieren in meiner richtigen Tabelle ebenfalls!!
Aufgrund der komplexen Tabelle habe ich für die Werte4 eine andere Formel...nämlich diese: =WENN(B2=ISTLEER(Wert);" ";B2*100+C2) :rolleyes:
Sonst wird mit weiter unten in meiner Tabelle als Ergebnis immer "#WERT" angezeigt, wenn ich die entsprechenden Zellen nicht ausgefüllt habe.

Die Formel für "I" habe ich ebenfalls etwas angepasst, nämlich so für z.B. I10 (71-100): =SUMMENPRODUKT(($D$2:$D$6>=71)*($D$2:$D$6<=100)*$A$2:$A$6)
Dies funktioniert ebenfalls und ich spare mir eine Zelle, da meine Tabelle sonst recht unübersichtlich werden würde.

Die Formeln für B9 und E9 habe ich 1zu1 übernehmen können ;)

Alsoooo, vielen vielen Dank für die Hilfe Josef (oder Sepp?)
Jetzt ist meine Tabelle endlich perfektioniert =)

Hajo_Zi
17.09.2011, 11:48
Halllo Micha,

zur Tabellendarstellung gibt es einige tolls.
Ich benutze

<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:106px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr></table><b>Bedingte Formatierung wird in dieser Tabelle nicht dargestellt</b><br/><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:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel </td><td> Bereich </td></tr><tr><td>A6 </td><td>=WENN(INDIREKT(B$6&amp;"!$f$2")="";"&nbsp;";INDIREKT(B$6&amp;"!$E$2"))</td><td>&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; width:800px; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td colspan="10" > bedingte Formatierung </td></tr><tr valign="top" style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td> Zelle &nbsp; </td><td> Typ &nbsp; </td><td> Operator &nbsp; </td><td> Formel1 &nbsp; </td><td> Formel2 &nbsp; </td><td> Format &nbsp; </td><td> Unterstrichen &nbsp; </td><td> Schrift- farbe </td><td> Muster &nbsp; </td><td> Muster- farbe </td></tr><tr><td>A6</td><td style="text-align:left; ">1.Bed.: Formel ist </td><td style="text-align:right; "> &nbsp; </td><td style="text-align:right; ">=GLÄTTEN(A6)=""</td><td style="text-align:right; "> &nbsp; </td><td style="background-color:#00B050; text-align:right; " >5287936</td><td style="text-align:right; "> &nbsp; </td><td>&nbsp;</td><td style="text-align:right; "> &nbsp; </td><td> &nbsp; </td></tr></table><b>Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen<br/>Durch die veränderte Bedingte Formatierung kann es vorkommen, dass Bedingungen falsch ausgelesen werden</b><br/><br/><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.46</a><br/><br/>
mit diesem Tool kann man die Tabelle aus dem Internet auch wieder in eine Tabelle umwandeln.

<a href="http://Hajo-Excel.de/index.htm" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>

Micha890
17.09.2011, 12:04
Kurzer Test...habe Jeanie, sowie auch Hajos Version nun eingebunden bekommen =)

<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:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><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></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="color:#FF0000; background-color:#FFFFFF; text-align:center; " ><b>Anzahl</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >Wert1</td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >Wert2</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " ><b>Ges-Wert</b></td><td style="color:#000000; background-color:#FFFF00; text-align:center; " >Wert4</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >Option1</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >Option2</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >Option3</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >Option4</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >Option5</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#FF0000; background-color:#C0C0C0; text-align:center; " ><b>1</b></td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >30</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >30</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " ><b>60</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >3030</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; ">&nbsp; </td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; ">&nbsp; </td><td style="color:#000000; background-color:#00FF00; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#FF0000; background-color:#FFFFFF; text-align:center; " ><b>2</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >60</td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >20</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " ><b>80</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >6020</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; ">&nbsp; </td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="color:#FF0000; background-color:#C0C0C0; text-align:center; " ><b>3</b></td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >50</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >70</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " ><b>120</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >5070</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; ">&nbsp; </td><td style="color:#000000; background-color:#00FF00; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="color:#FF0000; background-color:#FFFFFF; text-align:center; " ><b>4</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >60</td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >20</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " ><b>80</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >6020</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; ">&nbsp; </td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="color:#FF0000; background-color:#C0C0C0; text-align:center; " ><b>5</b></td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >70</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >30</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " ><b>100</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:center; " >7030</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >x</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td colspan="10" rowspan="1" style="color:#000000; background-color:#FF0000 ;; text-align:center; " ><b>Mengen&nbsp;summiert</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style="color:#000000; background-color:#FFFF00; text-align:left; " >3030</td><td style="color:#000000; background-color:#FFFF00; text-align:center; " >1</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#00FF00; text-align:left; " >Option1</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >15</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#00CCFF; text-align:left; " >0-70</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " >1</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style="color:#000000; background-color:#FFFF00; text-align:left; " >4050</td><td style="color:#000000; background-color:#FFFF00; text-align:center; " >0</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#00FF00; text-align:left; " >Option2</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >10</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#00CCFF; text-align:left; " >71-100</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " >11</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">11</td><td style="color:#000000; background-color:#FFFF00; text-align:left; " >5070</td><td style="color:#000000; background-color:#FFFF00; text-align:center; " >3</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#00FF00; text-align:left; " >Option3</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >13</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#00CCFF; text-align:left; " >101-150</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " >3</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">12</td><td style="color:#000000; background-color:#FFFF00; text-align:left; " >6020</td><td style="color:#000000; background-color:#FFFF00; text-align:center; " >6</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#00FF00; text-align:left; " >Option4</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >11</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#00CCFF; text-align:left; " >151-200</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " >0</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">13</td><td style="color:#000000; background-color:#FFFF00; text-align:left; " >5040</td><td style="color:#000000; background-color:#FFFF00; text-align:center; " >0</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#00FF00; text-align:left; " >Option5</td><td style="color:#000000; background-color:#00FF00; text-align:center; " >9</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#00CCFF; text-align:left; " >201-230</td><td style="color:#000000; background-color:#00CCFF; text-align:center; " >0</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">14</td><td style="color:#000000; background-color:#FFFF00; text-align:left; " >7030</td><td style="color:#000000; background-color:#FFFF00; text-align:center; " >5</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">15</td><td style="color:#000000; background-color:#FFFF00; text-align:left; " >8070</td><td style="color:#000000; background-color:#FFFF00; text-align:center; " >0</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&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> verbundene Zellen </td></tr><tr><td>A8:J8</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:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel </td><td> Bereich </td></tr><tr><td>D2 </td><td>=SUMME(B2:C2)</td><td>&nbsp;</td></tr><tr><td>E2 </td><td>=WENN(B2=ISTLEER(Wert);"&nbsp;";B2*100+C2)</td><td>&nbsp;</td></tr><tr><td>D3 </td><td>=SUMME(B3:C3)</td><td>&nbsp;</td></tr><tr><td>E3 </td><td>=WENN(B3=ISTLEER(Wert);"&nbsp;";B3*100+C3)</td><td>&nbsp;</td></tr><tr><td>D4 </td><td>=SUMME(B4:C4)</td><td>&nbsp;</td></tr><tr><td>E4 </td><td>=WENN(B4=ISTLEER(Wert);"&nbsp;";B4*100+C4)</td><td>&nbsp;</td></tr><tr><td>D5 </td><td>=SUMME(B5:C5)</td><td>&nbsp;</td></tr><tr><td>E5 </td><td>=WENN(B5=ISTLEER(Wert);"&nbsp;";B5*100+C5)</td><td>&nbsp;</td></tr><tr><td>D6 </td><td>=SUMME(B6:C6)</td><td>&nbsp;</td></tr><tr><td>E6 </td><td>=WENN(B6=ISTLEER(Wert);"&nbsp;";B6*100+C6)</td><td>&nbsp;</td></tr><tr><td>B9 </td><td>=SUMMENPRODUKT(($E$2:$E$6=A9)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>E9 </td><td>=SUMMENPRODUKT(($F$1:$J$1=D9)*($F$2:$J$6="x")*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>H9 </td><td>=SUMMENPRODUKT(($D$2:$D$6&gt;=0)*($D$2:$D$6&lt;=70)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>B10 </td><td>=SUMMENPRODUKT(($E$2:$E$6=A10)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>E10 </td><td>=SUMMENPRODUKT(($F$1:$J$1=D10)*($F$2:$J$6="x")*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>H10 </td><td>=SUMMENPRODUKT(($D$2:$D$6&gt;=71)*($D$2:$D$6&lt;=100)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>B11 </td><td>=SUMMENPRODUKT(($E$2:$E$6=A11)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>E11 </td><td>=SUMMENPRODUKT(($F$1:$J$1=D11)*($F$2:$J$6="x")*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>H11 </td><td>=SUMMENPRODUKT(($D$2:$D$6&gt;=101)*($D$2:$D$6&lt;=150)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>B12 </td><td>=SUMMENPRODUKT(($E$2:$E$6=A12)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>E12 </td><td>=SUMMENPRODUKT(($F$1:$J$1=D12)*($F$2:$J$6="x")*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>H12 </td><td>=SUMMENPRODUKT(($D$2:$D$6&gt;=151)*($D$2:$D$6&lt;=200)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>B13 </td><td>=SUMMENPRODUKT(($E$2:$E$6=A13)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>E13 </td><td>=SUMMENPRODUKT(($F$1:$J$1=D13)*($F$2:$J$6="x")*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>H13 </td><td>=SUMMENPRODUKT(($D$2:$D$6&gt;=201)*($D$2:$D$6&lt;=230)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>B14 </td><td>=SUMMENPRODUKT(($E$2:$E$6=A14)*$A$2:$A$6)</td><td>&nbsp;</td></tr><tr><td>B15 </td><td>=SUMMENPRODUKT(($E$2:$E$6=A15)*$A$2:$A$6)</td><td>&nbsp;</td></tr></table><br/><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.46</a><br/><br/>

Micha890
17.09.2011, 12:09
Und Jeanie funktioniert ebenfalls =) Danke nochmal !!

<b>Tabelle1</b><br /><br /><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;" /></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></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="color:#ff0000; font-weight:bold; text-align:center; ">Anzahl</td><td style="text-align:center; ">Wert1</td><td style="text-align:center; ">Wert2</td><td style="background-color:#00ccff; font-weight:bold; text-align:center; ">Ges-Wert</td><td style="background-color:#ffff00; text-align:center; ">Wert4</td><td style="background-color:#00ff00; text-align:center; ">Option1</td><td style="background-color:#00ff00; text-align:center; ">Option2</td><td style="background-color:#00ff00; text-align:center; ">Option3</td><td style="background-color:#00ff00; text-align:center; ">Option4</td><td style="background-color:#00ff00; text-align:center; ">Option5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#c0c0c0; color:#ff0000; font-weight:bold; text-align:center; ">1</td><td style="background-color:#c0c0c0; text-align:center; ">30</td><td style="background-color:#c0c0c0; text-align:center; ">30</td><td style="background-color:#00ccff; font-weight:bold; text-align:center; ">60</td><td style="text-align:center; ">3030</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; ">&nbsp;</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; ">&nbsp;</td><td style="background-color:#00ff00; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#ff0000; font-weight:bold; text-align:center; ">2</td><td style="text-align:center; ">60</td><td style="text-align:center; ">20</td><td style="background-color:#00ccff; font-weight:bold; text-align:center; ">80</td><td style="text-align:center; ">6020</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; ">&nbsp;</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#c0c0c0; color:#ff0000; font-weight:bold; text-align:center; ">3</td><td style="background-color:#c0c0c0; text-align:center; ">50</td><td style="background-color:#c0c0c0; text-align:center; ">70</td><td style="background-color:#00ccff; font-weight:bold; text-align:center; ">120</td><td style="text-align:center; ">5070</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; ">&nbsp;</td><td style="background-color:#00ff00; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#ff0000; font-weight:bold; text-align:center; ">4</td><td style="text-align:center; ">60</td><td style="text-align:center; ">20</td><td style="background-color:#00ccff; font-weight:bold; text-align:center; ">80</td><td style="text-align:center; ">6020</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; ">&nbsp;</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; text-align:center; ">x</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#c0c0c0; color:#ff0000; font-weight:bold; text-align:center; ">5</td><td style="background-color:#c0c0c0; text-align:center; ">70</td><td style="background-color:#c0c0c0; text-align:center; ">30</td><td style="background-color:#00ccff; font-weight:bold; text-align:center; ">100</td><td style="text-align:center; ">7030</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; text-align:center; ">x</td><td style="background-color:#00ff00; text-align:center; ">x</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><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td colspan="10" style="background-color:#ff0000; font-weight:bold; text-align:center; ">Mengen summiert</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffff00; text-align:left; ">3030</td><td style="background-color:#ffff00; text-align:center; ">1</td><td >&nbsp;</td><td style="background-color:#00ff00; text-align:left; ">Option1</td><td style="background-color:#00ff00; text-align:center; ">15</td><td >&nbsp;</td><td style="background-color:#00ccff; ">0-70</td><td style="background-color:#00ccff; text-align:center; ">1</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="background-color:#ffff00; text-align:left; ">4050</td><td style="background-color:#ffff00; text-align:center; ">0</td><td >&nbsp;</td><td style="background-color:#00ff00; text-align:left; ">Option2</td><td style="background-color:#00ff00; text-align:center; ">10</td><td >&nbsp;</td><td style="background-color:#00ccff; ">71-100</td><td style="background-color:#00ccff; text-align:center; ">11</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 style="background-color:#ffff00; text-align:left; ">5070</td><td style="background-color:#ffff00; text-align:center; ">3</td><td >&nbsp;</td><td style="background-color:#00ff00; text-align:left; ">Option3</td><td style="background-color:#00ff00; text-align:center; ">13</td><td >&nbsp;</td><td style="background-color:#00ccff; ">101-150</td><td style="background-color:#00ccff; text-align:center; ">3</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffff00; text-align:left; ">6020</td><td style="background-color:#ffff00; text-align:center; ">6</td><td >&nbsp;</td><td style="background-color:#00ff00; text-align:left; ">Option4</td><td style="background-color:#00ff00; text-align:center; ">11</td><td >&nbsp;</td><td style="background-color:#00ccff; ">151-200</td><td style="background-color:#00ccff; text-align:center; ">0</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="background-color:#ffff00; text-align:left; ">5040</td><td style="background-color:#ffff00; text-align:center; ">0</td><td >&nbsp;</td><td style="background-color:#00ff00; text-align:left; ">Option5</td><td style="background-color:#00ff00; text-align:center; ">9</td><td >&nbsp;</td><td style="background-color:#00ccff; ">201-230</td><td style="background-color:#00ccff; text-align:center; ">0</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffff00; text-align:left; ">7030</td><td style="background-color:#ffff00; 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></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#ffff00; text-align:left; ">8070</td><td style="background-color:#ffff00; text-align:center; ">0</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></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 >D2</td><td >=SUMME(B2:C2)</td></tr><tr><td >E2</td><td >=WENN(B2=ISTLEER<span style=' color:008000; '>(Wert)</span>;" ";B2*100+C2)</td></tr><tr><td >D3</td><td >=SUMME(B3:C3)</td></tr><tr><td >E3</td><td >=WENN(B3=ISTLEER<span style=' color:008000; '>(Wert)</span>;" ";B3*100+C3)</td></tr><tr><td >D4</td><td >=SUMME(B4:C4)</td></tr><tr><td >E4</td><td >=WENN(B4=ISTLEER<span style=' color:008000; '>(Wert)</span>;" ";B4*100+C4)</td></tr><tr><td >D5</td><td >=SUMME(B5:C5)</td></tr><tr><td >E5</td><td >=WENN(B5=ISTLEER<span style=' color:008000; '>(Wert)</span>;" ";B5*100+C5)</td></tr><tr><td >D6</td><td >=SUMME(B6:C6)</td></tr><tr><td >E6</td><td >=WENN(B6=ISTLEER<span style=' color:008000; '>(Wert)</span>;" ";B6*100+C6)</td></tr><tr><td >B9</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($E$2:$E$6=A9)</span>*$A$2:$A$6)</td></tr><tr><td >E9</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($F$1:$J$1=D9)</span>*<span style=' color:008000; '>($F$2:$J$6="x")</span>*$A$2:$A$6)</td></tr><tr><td >H9</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($D$2:$D$6&gt;=0)</span>*<span style=' color:008000; '>($D$2:$D$6&lt;=70)</span>*$A$2:$A$6)</td></tr><tr><td >B10</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($E$2:$E$6=A10)</span>*$A$2:$A$6)</td></tr><tr><td >E10</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($F$1:$J$1=D10)</span>*<span style=' color:008000; '>($F$2:$J$6="x")</span>*$A$2:$A$6)</td></tr><tr><td >H10</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($D$2:$D$6&gt;=71)</span>*<span style=' color:008000; '>($D$2:$D$6&lt;=100)</span>*$A$2:$A$6)</td></tr><tr><td >B11</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($E$2:$E$6=A11)</span>*$A$2:$A$6)</td></tr><tr><td >E11</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($F$1:$J$1=D11)</span>*<span style=' color:008000; '>($F$2:$J$6="x")</span>*$A$2:$A$6)</td></tr><tr><td >H11</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($D$2:$D$6&gt;=101)</span>*<span style=' color:008000; '>($D$2:$D$6&lt;=150)</span>*$A$2:$A$6)</td></tr><tr><td >B12</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($E$2:$E$6=A12)</span>*$A$2:$A$6)</td></tr><tr><td >E12</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($F$1:$J$1=D12)</span>*<span style=' color:008000; '>($F$2:$J$6="x")</span>*$A$2:$A$6)</td></tr><tr><td >H12</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($D$2:$D$6&gt;=151)</span>*<span style=' color:008000; '>($D$2:$D$6&lt;=200)</span>*$A$2:$A$6)</td></tr><tr><td >B13</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($E$2:$E$6=A13)</span>*$A$2:$A$6)</td></tr><tr><td >E13</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($F$1:$J$1=D13)</span>*<span style=' color:008000; '>($F$2:$J$6="x")</span>*$A$2:$A$6)</td></tr><tr><td >H13</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($D$2:$D$6&gt;=201)</span>*<span style=' color:008000; '>($D$2:$D$6&lt;=230)</span>*$A$2:$A$6)</td></tr><tr><td >B14</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($E$2:$E$6=A14)</span>*$A$2:$A$6)</td></tr><tr><td >B15</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>($E$2:$E$6=A15)</span>*$A$2:$A$6)</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>