Array Search and Add Formula Request

[timjs]

Here is a tiny sample of my data (actual sheet has 30 columns and about 1,000 rows):

<TABLE style="WIDTH: 305pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=405><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" span=2 width=117><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3340 width=117>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3340 width=117>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3340 width=74>C</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97 align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Pine Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Oak Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3339 width=74>8.5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Maple Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Elm Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3339 width=74>16.0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97 align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Oak Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Maple Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3339 width=74>3.5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97 align=right>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Elm Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Pine Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3339 width=74>28.5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97 align=right>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Oak Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Maple Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3339 width=74>17.5</TD></TR></TBODY></TABLE>

I cannot sort the data so that the wood trim names all appear in a single column (very long explanation, but it doesn't work). So what I need to do is to get a summary of the total length of each type of trim by wood species so that I might have another part of the spreadsheet that contains cells with the summary such as:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><o></o>

<TABLE style="WIDTH: 305pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=405><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3340 width=117>Species</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3340 width=117>Length</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3340 width=74></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97 align=right></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Pine Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>37</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3339 width=74></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97 align=right></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Maple Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>29.5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3339 width=74></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97 align=right></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Oak Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>44.5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3339 width=74></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97 align=right></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>Elm Trim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117>37</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3339 width=74></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 height=18 width=97 align=right></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3338 width=117></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl3339 width=74></TD></TR></TBODY></TABLE>

The best way that I've found to accomplish this is by using a formula in the "Pine Trim" "Length" cell that looks like:

=SUMIF($A$1:$A$5,”Pine Trim”,$C1$C5)+SUMIF($B$1:$B$5,”Pine Trim”,$C1$C5)

and of course the same formula for each "Length" cell for the other wood trim species.
I have tried all kinds of iterations of SUMPRODUCT, SUMIF, VLOOKUP, HLOOKUP, etc. and I've tried all kinds of permutations of nesting formulas and/or using array formula Control-Shift-Enter. So for example, even though the syntax for SUMIF is:

=SUMIF(range,criteria,[sum range])

the range is merely a range of cells in the SAME column and NOT an array! So if I were to condense my formula above into the following and specify the range as a 2-column, 5-row array:

=SUMIF($A$1:$B$5,”Pine Trim”,$C1$C5)

the result will only include "Pine Trim" listed in Column A, but NOT any entries from Column B, which is why I have added a SUMIF formula for each column. Gets very messy very fast so I'm looking for an abbreviated formula that will search the multi-column, multi-row array and account for and sum every length occurance for each species of wood trim. I don't want to do this without filters, pivot tables or VBA, just a plain formula in a cell (again the explanation as to why is much too long and I'm already taking forever).

Thanks so much and let me know if you need any clarification,

Tim

 

[markmzz]

Try this (array formula - use Ctrl+Shift+Enter and not only Enter):

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>Pine Trim</TD><TD>Oak Trim</TD><TD style="TEXT-ALIGN: right">8.5</TD><TD style="TEXT-ALIGN: right"></TD><TD>Species</TD><TD>Length</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Maple Trim</TD><TD>Elm Trim</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right"></TD><TD>Pine Trim</TD><TD style="TEXT-ALIGN: right">37</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Oak Trim</TD><TD>Maple Trim</TD><TD style="TEXT-ALIGN: right">3.5</TD><TD style="TEXT-ALIGN: right"></TD><TD>Maple Trim</TD><TD style="TEXT-ALIGN: right">37</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Elm Trim</TD><TD>Pine Trim</TD><TD style="TEXT-ALIGN: right">28.5</TD><TD style="TEXT-ALIGN: right"></TD><TD>Oak Trim</TD><TD style="TEXT-ALIGN: right">29.5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Oak Trim</TD><TD>Maple Trim</TD><TD style="TEXT-ALIGN: right">17.5</TD><TD style="TEXT-ALIGN: right"></TD><TD>Elm Trim</TD><TD style="TEXT-ALIGN: right">44.5</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; 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 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F2</TH><TD style="TEXT-ALIGN: left">{=SUM(($A$1:$B$5=E2)*($C$1:$C$5))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>


Markmzz

 

[timjs]

Mark,

Just incredible...I spent over 4 hours trying to crack this one open yesterday.

Thank you again & again!

Tim

 

[markmzz]

Mark,

Just incredible...I spent over 4 hours trying to crack this one open yesterday.

Thank you again & again!

Tim

Tim,

Thank you for give me the feedback.

Markmzz