Using table row & column heading for data extract summary

[eduardo1966]

Here is a problem that is driving me nuts. I have two tables. The first is generated from a trial balance with department-level detail. In this report I have department numbers going across and account numbers going down the page. The second table is a list of account and department combinations. I need to use this list to "cherry pick" the values in the trial balance report.

The following is a simplified example of the data I am working with:


Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Department</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="FONT-WEIGHT: bold">Account</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">100</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">200</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">300</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">400</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">500</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1947</TD><TD style="TEXT-ALIGN: right">443.77 </TD><TD style="TEXT-ALIGN: right">969.51 </TD><TD style="TEXT-ALIGN: right">652.91 </TD><TD style="TEXT-ALIGN: right">914.01 </TD><TD style="TEXT-ALIGN: right">448.99 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right">645.41 </TD><TD style="TEXT-ALIGN: right">413.39 </TD><TD style="TEXT-ALIGN: right">686.03 </TD><TD style="TEXT-ALIGN: right">341.49 </TD><TD style="TEXT-ALIGN: right">799.64 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2242</TD><TD style="TEXT-ALIGN: right">708.18 </TD><TD style="TEXT-ALIGN: right">95.08 </TD><TD style="TEXT-ALIGN: right">225.11 </TD><TD style="TEXT-ALIGN: right">272.31 </TD><TD style="TEXT-ALIGN: right">554.33 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2639</TD><TD style="TEXT-ALIGN: right">849.45 </TD><TD style="TEXT-ALIGN: right">52.08 </TD><TD style="TEXT-ALIGN: right">759.91 </TD><TD style="TEXT-ALIGN: right">739.21 </TD><TD style="TEXT-ALIGN: right">812.24 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">6400</TD><TD style="TEXT-ALIGN: right">698.19 </TD><TD style="TEXT-ALIGN: right">86.64 </TD><TD style="TEXT-ALIGN: right">412.01 </TD><TD style="TEXT-ALIGN: right">90.86 </TD><TD style="TEXT-ALIGN: right">505.13 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Data Needed for</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Account</TD><TD style="FONT-WEIGHT: bold">Department</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1947</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">200</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">100</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">200</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">300</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2639</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">500</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">6400</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">300</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>Function would return</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">3,938.59 </TD><TD>sum of cells C3, B4, C4, D4, F6, & D7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

I can get an answer the old-fashioned way with index and match formulas running down the second list, but I've got to believe there is a tighter alternative. Ideally, I would like the formula to be elegant and in just one cell. I thought using "countif" would do the trick, but there is a limitation in that function that I just can't get past. Any ideas out there?

 

[eduardo1966]

rajesh, that's a nice alternative. thanks!

 

[mgirvin]

Yes, I wonder about:

=SUM(INDEX(B3:F7,MATCH(C11:C16,A3:A7),MATCH(D11:D16,B2:F2)))

Because the MATCH does deliver an array and the INDEX array argument will handle arrays, but together they do not work to pull all the values. I guess it is just the way INDEX is programmed.

The other thing that I wonder about is output from INDIRECT(ADDRESS()), which is:

{969.51;645.41;413.39;686.03;812.24;412.01}

why can't the SUM just read it? How is it that TRANSPOSE can translate it into a form that SUM can read? I have looked in help for TRANSPOSE and do not see anything about how it would help the SUM to read the INDIRECT(ADDRESS()) output.

Somehow the SUM can't read the vertical array:

{969.51;645.41;413.39;686.03;812.24;412.01}

but it can read the horizontal array (output from TRANSPOSE):

{969.51,645.41,413.39,686.03,812.24,412.01}

That can't possibly be the reason, right? Becasue the SUM can handle cell ranges that are horizontal or vertical.

There is a lot of cool Excel Magic going on in the formula that circledchicken posted!!

 

[mgirvin]

eduardo1966, I got the formula to work from a different sheet like this:

Table on sheet "Table":

Excel Workbook
	A	B	C	D	E	F
1		Department
2	Account	100	200	300	400	500
3	1947	443.77	969.51	652.91	914.01	448.99
4	2240	645.41	413.39	686.03	341.49	799.64
5	2242	708.18	95.08	225.11	272.31	554.33
6	2639	849.45	52.08	759.91	739.21	812.24
7	6400	698.19	86.64	412.01	90.86	505.13
Table

Formula and Criteria on sheet named "Formula":

Excel Workbook
	A	B
1	Account	Department
2	1947	200
3	2240	100
4	2240	200
5	2240	300
6	2639	500
7	6400	300
8
9	Total	3938.59
Formula

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[eduardo1966]

Mike, Thanks for the example. Even working with this small data set and setting up the worksheets exactly as you described I'm getting an #N/A error.

When I highlight everything from "TRANSPOSE" through the next-to-last parenthesis and hit F9, I see the array I want to evaluate, and if I make this an array constant and hit Ctrl-Shift-Enter, I get the right sum value. But the formula exactly as you set up, will not work on my system, and I've been very careful not to make any mistakes.

I'm thinking that it might be an Excel version issue as my employer has me on Excel 2007, and I suspect you might be on Excel 2010. Tonight I'm going to try this on my home computer where I have Excel 2010 installed to test this theory.

Thanks,

Eduardo

 

[circledchicken]

Hi all,

Many thanks for your kind comments and suggestions!

Here is a variation on your formula that gets rid of the + 2 and +1 by simply extending the ranges in MATCH:

=SUM(TRANSPOSE(INDIRECT(ADDRESS(MATCH(C11:C16,A1:A7,0),MATCH(D11:D16,A2:F2,0)))))

Thanks for this Mike, it certainly makes it read better. I wanted to avoid extending the ranges in the (unlikely) event that one of the column or row headings is repeated within the extended part, leading to an incorrect answer that might be difficult to identify.

I tried the Index approach too - unfortunately I'm not sure either why it doesn't work as we might like it to!

Your point about the use of TRANSPOSE is also interesting - it is not the only method we can use:

• If you evaluate the formula without using TRANSPOSE (with F9 in the formula bar or formula evaluator) you will get the correct result - but the output to the sheet is different.
  
• To see this more clearly, you can replace TRANSPOSE with SUM, then wrap the whole formula in a second SUM and you should get the correct result too i.e.
  
  =SUM(SUM((INDIRECT(ADDRESS(MATCH(C11:C16,A1:A7,0),MATCH(D11:D16,A2:F2,0))))))
  
  Since the inner SUM already gives us the correct result, the outer function can really be anything i.e.
  
  =AVERAGE(SUM((INDIRECT(ADDRESS(MATCH(C11:C16,A1:A7,0),MATCH(D11:D16,A2:F2,0)))))) or
  =PRODUCT(SUM((INDIRECT(ADDRESS(MATCH(C11:C16,A1:A7,0),MATCH(D11:D16,A2:F2,0)))))) or
  =TRANSPOSE(SUM((INDIRECT(ADDRESS(MATCH(C11:C16,A1:A7,0),MATCH(D11:D16,A2:F2,0))))))
  
  Using SUMPRODUCT we can eliminate the need to array enter the formula, so that it will work with just ENTER (although I'm not sure its 'healthy' to use SUMPRODUCT simply to eliminate the need to CTRL-SHIFT-ENTER):
  =SUMPRODUCT(SUM((INDIRECT(ADDRESS(MATCH(C11:C16,A1:A7,0),MATCH(D11:D16,A2:F2,0))))))

Eduardo - Mike's formula with the address parameters works for me (Excel 2002).

Arul - I like you function too - very nice - one question I have is your upper bound for the columns - I would normally say UBound(Datr,2) - is there any reason/advantage to using Transpose to figure out that bound?

Finally on a side note - Mike I love your Youtube videos, they're awesome. Can you do more duelling Excel ones with MrExcel please?! I have honestly learnt a huge amount from watching those videos - extremely helpful when I started learning Excel and now whenever I need to understand some new function or way of doing something - so a very big thankyou to you!

 

[Arul.rajesh]

Arul - I like you function too - very nice - one question I have is your upper bound for the columns - I would normally say UBound(Datr,2) - is there any reason/advantage to using Transpose to figure out that bound?

Thanks, I didn't know that. Guess I should have looked at help for UBOUND instead of going through the trouble of using application.worksheetfunction.

I used the immediate window and kept looking at watches while I ran the sub thingy and finally modified it to a function thingy. .

Never understood array functions though. The help on array functions in excel is not very helpful. Guess there will something on this forum in it... will keep searching.

 

[mgirvin]

circledchicken,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
You have the coolest name! What is the story behind how you got the name circledchicken?
<o></o>
What you said about the fact that many different functions could be in place of the TRANSPOSE is very interesting. I kind of like this then:
<o></o>
=SUMPRODUCT(N(INDIRECT(ADDRESS(MATCH(C11:C16,A3:A7,0)+2,MATCH(D11:D16,B2:F2,0)+1))))
<o></o>
I think it is fine to use the SUMPRODUCT function to avoid Ctrl + Shift + Enter (unless it would cause longer calculating time – which I don’t think it would in this case). The cases that it is ‘unhealthy’ to use SUMPRODUCT are when the formula needs Ctrl + Shift + Enter regardless of whether you use the SUMPRODUICT (like if an array is in the logical_test argument of the IF function).
<o></o>
On a side-side note: I am glad that you find some use for the videos that I make. What is so cool about the online Excel community is that we all learn from each other! Woo Hoo!! I will definitely make a video about this amazing formula for "multiple two-way lookup adding"!

 

[circledchicken]

circledchicken,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
You have the coolest name! What is the story behind how you got the name circledchicken?
<o></o>
What you said about the fact that many different functions could be in place of the TRANSPOSE is very interesting. I kind of like this then:
<o></o>
=SUMPRODUCT(N(INDIRECT(ADDRESS(MATCH(C11:C16,A3:A7,0)+2,MATCH(D11:D16,B2:F2,0)+1))))
<o></o>
I think it is fine to use the SUMPRODUCT function to avoid Ctrl + Shift + Enter (unless it would cause longer calculating time – which I don’t think it would in this case). The cases that it is ‘unhealthy’ to use SUMPRODUCT are when the formula needs Ctrl + Shift + Enter regardless of whether you use the SUMPRODUICT (like if an array is in the logical_test argument of the IF function).
<o></o>
On a side-side note: I am glad that you find some use for the videos that I make. What is so cool about the online Excel community is that we all learn from each other! Woo Hoo!! I will definitely make a video about this amazing formula for "multiple two-way lookup adding"!

Haha thanks Mike! ...A pigeon crashed into my window one day, and walked in circles before flying away. It kind of reminded me of this Kay Ryan poem (on a side-side-side note, I like her poems!) - http://www.poets.org/viewmedia.php/prmMID/20197 - and yeah thats it!

Anyway back to Excel .. - awesome, I like that form (I've never used the N function before). Also thanks for the explanation about SUMPRODUCT & CTRL-SHIFT-ENTER. The behaviour of the later still confuses me in some cases, I'm sure I'll find a little clarification in one of your videos.

Woo Hoo!! exactly! - Your videos + this forum + many other websites are full of very helpful and clever people - helped me loads. A video on this would be amazing - with the usual Excelisfun spirit - can't wait!

 

[circledchicken]

Thanks, I didn't know that. Guess I should have looked at help for UBOUND instead of going through the trouble of using application.worksheetfunction.

I used the immediate window and kept looking at watches while I ran the sub thingy and finally modified it to a function thingy. .

Never understood array functions though. The help on array functions in excel is not very helpful. Guess there will something on this forum in it... will keep searching.

Arul,

Yes, I'm sure this forum is full of information about array formula's. Other resources:

• Mike's array formula series - http://www.youtube.com/watch?v=vqfikSMx7mE
• Chip Pearson's website - http://www.cpearson.com/excel/ArrayFormulas.aspx
• Microsoft website - http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx
• + I'm sure there's loads more all over the place

 

[mgirvin]

circledchicken,

That is a really cool story about how you got that name! Love the poem!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The confusing over array formulas is common because there is no good record (that I have seen) of 1) which arguments demand Ctrl + Shift + Enter all the time and 2) which ones require it when they are alone in a cell, but do not require it when they are in SUMPRODUCT. For Example, the IF function will require Ctrl + Shift + Enter all the time and the MATCH or SEARCH would require it if the formula where entered in a cell by itself, but if it were inside a SUMPRODUCT it would not require it. For example, here are two formulas to count unique items that both require Ctrl + Shift + Enter:<o></o>
<o></o>
=SUM(IF(FREQUENCY(IF(A16:A27<>"",MATCH("~"&A16:A27,A16:A27&"",0)),ROW(A16:A27)-ROW(A16)+1),1))<o></o>
<o></o>
=SUMPRODUCT(IF(FREQUENCY(IF(A16:A27<>"",MATCH("~"&A16:A27,A16:A27&"",0)),ROW(A16:A27)-ROW(A16)+1),1))<o></o>
<o></o>
In both cases the IF has an array of items entered into its arguments and so you must use Ctrl + Shift + Enter no matter if you use SUM or SUMPRODUCT. So the thinking goes, use the SUM instead of SUMPODUCT because then the user will have less chance of misinterpreting how the formula should be entered (with Ctrl + Shift + Enter).<o></o>
<o></o>
Here is a video on this exact subject (and way below is a Message Board example): <o></o>
Excel Magic Trick 620: Count Unique Items in List - Robust Formulas & A Few Array Formula Tricks <o></o>
http://www.youtube.com/watch?v=0QNznFt0El4<o></o>
<o></o>
By the way, the link you gave for a list of array videos that people can learn from should be this link (this link has the Highline Excel Class Series of array formula videos, which are more complete than the first series I posted 3 years ago):<o></o>
Excel Array Formulas Beg - Advanced <o></o>
http://www.youtube.com/view_play_list?p=007E7E9CA63304D3<o></o>
<o></o>
I will definitely make a video about the cool formula you posted (hopefully within the next week)!!






<TABLE style="WIDTH: 770pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1026><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 9pt; mso-width-source: userset; mso-width-alt: 438" width=12><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 564pt; mso-width-source: userset; mso-width-alt: 27501" width=752><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20 width=64>Names</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 11pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=14></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=100>Count Unique</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 9pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=12></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=84></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 564pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=752></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>Sioux</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=middle>#VALUE!</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">No CSE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">=SUMPRODUCT(IF(FREQUENCY(IF(A2:A13<>"",MATCH("~"&A2:A13,A2:A13&"",0)),ROW(A2:A13)-ROW(A2)+1),1))</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Sioux</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Yes CSE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">{=SUM(IF(FREQUENCY(IF(A2:A13<>"",MATCH("~"&A2:A13,A2:A13&"",0)),ROW(A2:A13)-ROW(A2)+1),1))}</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>*Sioux</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Sue</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Jo</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>*Jo</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Joe</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>