<title>Excel Jeanie HTML</title>Dear Array Gurus, this is driving me nuts!
Why does F1 and I1 return #N/A?
Same question for the break-down method is in columns G and H. None of them work.
The only thing that fixes it is to do a copy/paste values in column E.
The other strange part is that the array formuale all work when I select them in the formula bar and press F9 and they all work when I first enter them before I re-calc the spreadsheet (when calculation is set to manual).
Am I going crazy or is this just a bug in Excel?
Groups
<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Verdana,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 97px;"> <col style="width: 72px;"> <col style="width: 14px;"> <col style="width: 44px;"> <col style="width: 53px;"> <col style="width: 49px;"> <col style="width: 41px;"> <col style="width: 41px;"> <col style="width: 41px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </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></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">Item</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">In Group</td> <td style="font-weight: bold;"> </td> <td style="font-weight: bold; background-color: rgb(255, 204, 0);">Item</td> <td style="font-weight: bold; background-color: rgb(255, 204, 0);">Group</td> <td style="font-weight: bold; color: rgb(255, 0, 0); background-color: rgb(255, 255, 0);">#N/A</td> <td style="background-color: rgb(51, 204, 204);"> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(255, 204, 153);">#N/A</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">1</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">27</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">1</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">27</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">1</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">2</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">0</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">3</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">109</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">3</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">109</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">5</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">82</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">3</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">4</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">0</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">7</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">165</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">4</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">5</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">82</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">9</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">35</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">5</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">6</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">0</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">10</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">38</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">6</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">7</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">165</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">12</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">215</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">7</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">8</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">0</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">13</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">211</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">8</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">9</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">35</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">10</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">38</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">11</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">0</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">12</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">215</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">13</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">211</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0); text-align: right;">13</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0); text-align: right;">8</td> <td style="font-weight: bold;"> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td>CountInGroup</td> <td>=Groups!$B$15</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td>Group</td> <td>=OFFSET(Groups!$E$2,0,0,CountInGroup)</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>F1</td> <td>{=SUM(IF(MATCH(Group,Group,0)=(ROW(Group)-ROW($E2)+1),1,0))}</td></tr> <tr> <td>H1</td> <td>{=SUM(IF(OFFSET(G2,0,0,CountInGroup)=(ROW(OFFSET(G2,0,0,CountInGroup))-1),1,0))}</td></tr> <tr> <td>I1</td> <td>{=SUM(IF(FREQUENCY(IF(Group<>"",MATCH("~"&Group,Group&"",0)),ROW(Group)-ROW(E2)+1),1))}</td></tr> <tr> <td>D2</td> <td>{=IF(ROW(INDIRECT("1:"&$A$15))<=CountInGroup,INDEX(Groups!$A$2:$A$14,SMALL(IF(Groups!$B$2:$B$14<>0,ROW(Groups!$B$2:$B$14)-1),ROW(INDIRECT("1:"&$A$15)))),0)}</td></tr> <tr> <td>E2</td> <td>{=IF(OFFSET($D2,0,0,$A$15)=0,0,INDEX(Groups!$B$2:$B$14,OFFSET($D2,0,0,$A$15)))}</td></tr> <tr> <td>G2</td> <td>{=MATCH(Group,Group,0)}</td></tr> <tr> <td>H2</td> <td>{=(ROW(Group)-ROW($E2)+1)}</td></tr> <tr> <td>A15</td> <td>=MAX(A2:A14)</td></tr> <tr> <td>B15</td> <td>=COUNTIF(B2:B14,"<>0")</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>
Why does F1 and I1 return #N/A?
Same question for the break-down method is in columns G and H. None of them work.
The only thing that fixes it is to do a copy/paste values in column E.
The other strange part is that the array formuale all work when I select them in the formula bar and press F9 and they all work when I first enter them before I re-calc the spreadsheet (when calculation is set to manual).
Am I going crazy or is this just a bug in Excel?
Groups
<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Verdana,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 97px;"> <col style="width: 72px;"> <col style="width: 14px;"> <col style="width: 44px;"> <col style="width: 53px;"> <col style="width: 49px;"> <col style="width: 41px;"> <col style="width: 41px;"> <col style="width: 41px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </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></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">Item</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">In Group</td> <td style="font-weight: bold;"> </td> <td style="font-weight: bold; background-color: rgb(255, 204, 0);">Item</td> <td style="font-weight: bold; background-color: rgb(255, 204, 0);">Group</td> <td style="font-weight: bold; color: rgb(255, 0, 0); background-color: rgb(255, 255, 0);">#N/A</td> <td style="background-color: rgb(51, 204, 204);"> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(255, 204, 153);">#N/A</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">1</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">27</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">1</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">27</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">1</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">2</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">0</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">3</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">109</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">3</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">109</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">5</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">82</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">3</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">4</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">0</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">7</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">165</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">4</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">5</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">82</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">9</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">35</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">5</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">6</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">0</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">10</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">38</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">6</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">7</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">165</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">12</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">215</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">7</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">8</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">0</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">13</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">211</td> <td> </td> <td style="background-color: rgb(51, 204, 204);">#N/A</td> <td style="background-color: rgb(51, 204, 204); text-align: right;">8</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">9</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">35</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">10</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">38</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">11</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">0</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">12</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">215</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">13</td> <td style="background-color: rgb(255, 255, 0); text-align: right;">211</td> <td> </td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td style="background-color: rgb(255, 204, 0); text-align: right;">0</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0); text-align: right;">13</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0); text-align: right;">8</td> <td style="font-weight: bold;"> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td>CountInGroup</td> <td>=Groups!$B$15</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td>Group</td> <td>=OFFSET(Groups!$E$2,0,0,CountInGroup)</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>F1</td> <td>{=SUM(IF(MATCH(Group,Group,0)=(ROW(Group)-ROW($E2)+1),1,0))}</td></tr> <tr> <td>H1</td> <td>{=SUM(IF(OFFSET(G2,0,0,CountInGroup)=(ROW(OFFSET(G2,0,0,CountInGroup))-1),1,0))}</td></tr> <tr> <td>I1</td> <td>{=SUM(IF(FREQUENCY(IF(Group<>"",MATCH("~"&Group,Group&"",0)),ROW(Group)-ROW(E2)+1),1))}</td></tr> <tr> <td>D2</td> <td>{=IF(ROW(INDIRECT("1:"&$A$15))<=CountInGroup,INDEX(Groups!$A$2:$A$14,SMALL(IF(Groups!$B$2:$B$14<>0,ROW(Groups!$B$2:$B$14)-1),ROW(INDIRECT("1:"&$A$15)))),0)}</td></tr> <tr> <td>E2</td> <td>{=IF(OFFSET($D2,0,0,$A$15)=0,0,INDEX(Groups!$B$2:$B$14,OFFSET($D2,0,0,$A$15)))}</td></tr> <tr> <td>G2</td> <td>{=MATCH(Group,Group,0)}</td></tr> <tr> <td>H2</td> <td>{=(ROW(Group)-ROW($E2)+1)}</td></tr> <tr> <td>A15</td> <td>=MAX(A2:A14)</td></tr> <tr> <td>B15</td> <td>=COUNTIF(B2:B14,"<>0")</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>
