PDA

Vollständige Version anzeigen : Zählen - 3. Kriterium aus Liste einbinden


zorraldo
03.04.2012, 13:04
Hallo,

könnte ich bitte Unterstützung bei der Einbindung eines 3. Kriteriums in eine Summenformel erhalten:

Wie aus der beigefügten Datei zu ersehen ist soll in I1 nur gezählt werden, wenn

- in Spalte A ein "Ja" steht
- in Spalte D das Datum kleiner als das in B1 ist
- der § dem Kriterium 3 aus Spalte I entspricht

Mit

=SUMMENPRODUKT(($A$5:$A$44=G5)*($D$5:$D$44<B1))

erfülle ich die ersten beiden Kriterien - aber wie binde ich das Kriterium 3 ein?

Danke schon mal vorab

Gruß
Zorraldo

CitizenX
03.04.2012, 13:15
Hi,


<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='10' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:bold; font-style:normal; '><colgroup><col width='28pt'><col width='60pt'><col width='66,75pt'><col width='60pt'><col width='60pt'><col width='60pt'><col width='60pt'><col width='66,75pt'><col width='75,75pt'><col width='72,75pt'></colgroup><tr style='background-color:#FAFAFA'><td align='middle' colspan='10'>Arbeitsblatt mit dem Namen 'Tabelle1'</td></tr><tr style='background-color:#cacaca'><td>*</td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td><td align='middle'>F</td><td align='middle'>G</td><td align='middle'>H</td><td align='middle'>I</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='left' style='font-size:12px; ' >Datum</td><td align='right' style='font-size:12px; ' >03.04.2012</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='font-size:13px; ' >überfälliges Ende ("Ja" und Ende <Span><</Span> Heute):</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-size:14px; font-weight:normal; ' >*</td><td align='middle' >7</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' style='background-color:#C0C0C0; ' >Zähl-Fall</td><td align='middle' style='background-color:#C0C0C0; ' >§§</td><td align='middle' style='background-color:#C0C0C0; ' >Beginn</td><td align='middle' style='background-color:#C0C0C0; ' >Ende</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' style='background-color:#00FF00; ' >Kriterium 1</td><td align='middle' style='background-color:#00FF00; ' >Kriterium 2</td><td align='middle' style='background-color:#00FF00; ' >Kriterium 3</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 20</td><td align='right' style='font-weight:normal; ' >06.02.12</td><td align='right' style='font-weight:normal; ' >05.08.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='font-weight:normal; ' >Ende <Span><</Span> Datum</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 18.3</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 27.2</td><td align='right' style='font-weight:normal; ' >21.06.11</td><td align='right' style='background-color:#FF0000; font-weight:normal; ' >01.03.12</td><td align='left' style='font-weight:normal; ' >x</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 19</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 27.3</td><td align='right' style='font-weight:normal; ' >15.12.10</td><td align='right' style='font-weight:normal; ' >14.06.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 20</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 29</td><td align='right' style='font-weight:normal; ' >19.09.11</td><td align='right' style='background-color:#FF0000; font-weight:normal; ' >18.03.12</td><td align='left' style='font-weight:normal; ' >x</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 27.2</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 29/31</td><td align='right' style='font-weight:normal; ' >27.10.11</td><td align='right' style='font-weight:normal; ' >05.06.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 27.3</td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='middle' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 30</td><td align='right' style='font-weight:normal; ' >01.01.10</td><td align='right' style='background-color:#FF0000; font-weight:normal; ' >30.06.11</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 29</td></tr><tr><td style='background-color:#cacaca' align='middle'>11</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 31</td><td align='right' style='font-weight:normal; ' >14.09.11</td><td align='right' style='font-weight:normal; ' >30.11.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 29/31</td></tr><tr><td style='background-color:#cacaca' align='middle'>12</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 32</td><td align='right' style='font-weight:normal; ' >20.05.11</td><td align='right' style='background-color:#FF0000; font-weight:normal; ' >01.04.12</td><td align='left' style='font-weight:normal; ' >x</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 30</td></tr><tr><td style='background-color:#cacaca' align='middle'>13</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 33</td><td align='right' style='font-weight:normal; ' >14.09.11</td><td align='right' style='font-weight:normal; ' >30.11.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 31</td></tr><tr><td style='background-color:#cacaca' align='middle'>14</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='font-weight:normal; ' >§ 13.3</td><td align='right' style='font-weight:normal; ' >25.05.10</td><td align='right' style='background-color:#FF0000; font-weight:normal; ' >01.03.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 32</td></tr><tr><td style='background-color:#cacaca' align='middle'>15</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 35</td><td align='right' style='font-weight:normal; ' >25.05.10</td><td align='right' style='font-weight:normal; ' >24.06.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 33</td></tr><tr><td style='background-color:#cacaca' align='middle'>16</td><td align='middle' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 35a amb</td><td align='right' style='font-weight:normal; ' >22.02.11</td><td align='right' style='background-color:#FF0000; font-weight:normal; ' >01.04.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 34</td></tr><tr><td style='background-color:#cacaca' align='middle'>17</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 35a teilstat</td><td align='right' style='font-weight:normal; ' >15.06.10</td><td align='right' style='background-color:#FF0000; font-weight:normal; ' >01.02.12</td><td align='left' style='font-weight:normal; ' >x</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 35</td></tr><tr><td style='background-color:#cacaca' align='middle'>18</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 35a stat</td><td align='right' style='font-weight:normal; ' >23.11.10</td><td align='right' style='font-weight:normal; ' >22.05.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 35a amb</td></tr><tr><td style='background-color:#cacaca' align='middle'>19</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 42</td><td align='right' style='font-weight:normal; ' >07.12.07</td><td align='right' style='font-weight:normal; ' >06.07.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 35a teilstat</td></tr><tr><td style='background-color:#cacaca' align='middle'>20</td><td align='middle' style='font-weight:normal; ' >Ja</td><td align='left' style='font-weight:normal; ' >§ 8a</td><td align='right' style='font-weight:normal; ' >23.11.10</td><td align='right' style='font-weight:normal; ' >22.05.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 35a stat</td></tr><tr><td style='background-color:#cacaca' align='middle'>21</td><td align='middle' style='font-weight:normal; ' >*</td><td align='left' style='font-weight:normal; ' >§ 8a/ § 50</td><td align='right' style='font-weight:normal; ' >06.12.06</td><td align='right' style='background-color:#FF0000; font-weight:normal; ' >01.01.12</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='right' style='font-weight:normal; ' >*</td><td align='left' style='color:#0000FF; font-weight:normal; ' >§ 42</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:Arial; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Zelle</td><td>Formel</td></tr><tr><td>B1</td><td><Span style='color:#222222'>=HEUTE</Span><Span style='color:#0000DD'>()</Span><Span style='color:#222222'></Span></td></tr><tr><td>I1</td><td><Span style='color:#222222'>{=SUMME</Span><Span style='color:#0000DD'>(</Span><Span style='color:#222222'>($A$5:$A$44=G5)</Span><Span style='color:#0000DD'>*</Span><Span style='color:#222222'>($D$5:$D$44<Span style='color:#222222'><</Span>B1)</Span><Span style='color:#0000DD'>*</Span><Span style='color:#222222'>($B$5:$B$44=MTRANS</Span><Span style='color:#0000DD'>($I$5:$I$21)</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><table style='font-family:Arial; font-size:7pt'><tr><td style='color:#333333'>Diese Tabelle wurde mit Tab2Html (v2.4.0) erstellt. ©Gerd alias Bamberg</td></tr></table>

zorraldo
03.04.2012, 14:16
Hallo CitizenX,

danke für die schnelle Antwort.

"MTRAN" kann ich bisher noch nicht. Kommt in mein "Excel-Körbchen" ...

Schöne Ostern!

Zorraldo

Erich G.
03.04.2012, 16:06
Hi Steffen,
den Trick MTRANS für das 2. Array muss ich in mein Repertoire aufnehmen!
Ich kannte hier nur die Möglichkeit in J7:

<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:97px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >I</td><td >J</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ff99cc; font-weight:bold; font-size:14pt; text-align:right; ">7</td><td style="font-size:14pt; text-align:right; ">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 >I1</td><td >{=SUMME(<span style=' color:008000; '>($A$5:$A$44=G5)</span>*<span style=' color:008000; '>($D$5:$D$44&lt;B1)</span>*<span style=' color:008000; '>($B$5:$B$44=MTRANS<span style=' color:#0000ff; '>($I$5:$I$21)</span>)</span>)}</td></tr><tr><td >J1</td><td >{=SUMME(<span style=' color:008000; '>($A$5:$A$44=G5)</span>*<span style=' color:008000; '>($D$5:$D$44&lt;B1)</span>*Z&Auml;HLENWENN<span style=' color:008000; '>($I$5:$I$21;$B$5:$B$44)</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Enth&auml;lt Matrixformel:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Umrandende </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> nicht miteingeben,<br />sondern Formel mit STRG+SHIFT+RETURN abschlie&szlig;en!</span></td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=26" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Matrix verstehen</a></td></tr></table>

CitizenX
03.04.2012, 17:23
Hi Erich ,

es ist mir eine Ehre einen "alten" Formelhasen wie Dir was mit geben zu können..

apropos Hase -schöne Ostern! :D