Sumif, Index, Match

[tatourrea]

I have the following table:
A_________B_________C_____D_____E_____F
<TABLE style="WIDTH: 252pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=335 border=0><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><TBODY><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: transparent" width=50 height=35></TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=97></TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 30pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=40>GMC</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>FORD</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>GMC</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=46>GMC</TD></TR><TR style="HEIGHT: 42pt; mso-height-source: userset" height=56><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 42pt; BACKGROUND-COLOR: transparent" width=50 height=56>Area</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=97>Subarea</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 30pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=40>Acadia</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>Explorer</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>Terrain</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=46>Yucon</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>North</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1510</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1.0</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.0 </TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=17>North</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">1520</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" align=right>1.0</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none"></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>North</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1530</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2.0 </TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=17>North</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">1540</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" align=right>4.0</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">1.0 </TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>North</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1550</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.0 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=17>South</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">1670</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" align=right>1.0</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" align=right>2.0</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ebeaea; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">1.0 </TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>South</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1680</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1.0</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.0 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>


I am trying to summarize this table to see how many vehicles of each make I have in each area, but when I do the formula it only adds the vehicles in the first column for GMC.

The table bellow shows that for GMC in the North I am only getting 5 vehicles, that is the sum of only the first column, and its not summing up the other two columns that should bring me to a total of 10 GMC in the north

H__________I_______J
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 2pt double; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=64 height=17></TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=64>North</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=64>South</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17>GMC</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5.0</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.0</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17>FORD</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.0</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3.0</TD></TR></TBODY></TABLE>

The formula that I have is:

=SUMIF($A$3:$F$9,I$1,INDEX($C$3:$F$9,0,MATCH($H2,$C$1:$F$1,0)))

Is there onother way to get excel to look at the additional columns and not just the first one it finds?

Thanks

 

[Domenic]

Try...

I2, confirmed with CONTROL+SHIFT+ENTER, copied down and across:

=SUM(IF($A$3:$A$9=I$1,IF($C$1:$F$1=$H2,$C$3:$F$9)))

 

[tatourrea]

Thank you very much, didnt realize it was this simple, I was complicating myself too much.

 

[Domenic]

You're welcome! Thanks for the feedback!

 

[APonyLover]

Try...

I2, confirmed with CONTROL+SHIFT+ENTER, copied down and across:

=SUM(IF($A$3:$A$9=I$1,IF($C$1:$F$1=$H2,$C$3:$F$9)))

OMG Thank You, I was also over complicating this in my project. Also I had totally forgot about "CONTROL+SHIFT+ENTER" for Arrays. It's been a bit since I played with this stuff.

THANK YOU!!!!

 

[happyhungarian]

Is there a way to do this without an array formula or without the Sumproduct? I need to copy/paste my formula across many columns so the array formula is difficult and with that many instances of the sumproduct formula I'm afraid it'll slow down my sheet.

 

[DanteAmor]

Try this with auxiliary columns


<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <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:55px;" /><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 > </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:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; ">GMC</td><td style="background-color:#ffff00; ">FORD</td><td style="background-color:#ffff00; ">GMC</td><td style="background-color:#ffff00; ">GMC</td><td > </td><td > </td><td style="background-color:#92d050; ">GMC</td><td style="background-color:#92d050; ">FORD</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; ">Area</td><td style="background-color:#ffff00; ">Subarea</td><td style="background-color:#ffff00; ">Acadia</td><td style="background-color:#ffff00; ">Explorer</td><td style="background-color:#ffff00; ">Terrain</td><td style="background-color:#ffff00; ">Yucon</td><td > </td><td > </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >North</td><td style="text-align:right; ">1510</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >North</td><td style="text-align:right; ">1520</td><td > </td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >North</td><td style="text-align:right; ">1530</td><td > </td><td > </td><td > </td><td style="text-align:right; ">2</td><td > </td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >North</td><td style="text-align:right; ">1540</td><td style="text-align:right; ">4</td><td > </td><td > </td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >North</td><td style="text-align:right; ">1550</td><td > </td><td > </td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >South</td><td style="text-align:right; ">1670</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td > </td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >South</td><td style="text-align:right; ">1680</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#92d050; ">North</td><td style="text-align:right; ">10</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#92d050; ">South</td><td style="text-align:right; ">3</td><td style="text-align:right; ">3</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>Formulas</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 >Cell</td><td >Formula</td></tr><tr><td >I3</td><td >=SUMIF($C$1:$F$1,I$1,$C3:$F3)</td></tr><tr><td >J3</td><td >=SUMIF($C$1:$F$1,J$1,$C3:$F3)</td></tr><tr><td >I12</td><td >=SUMIF($A$3:$A$9,$H12,I$3:I$9)</td></tr><tr><td >J12</td><td >=SUMIF($A$3:$A$9,$H12,J$3:J$9)</td></tr></table></td></tr></table> <br /><br />