Hlookup与sum如何嵌套?

HLEx

<p><font face="Times New Roman" color="#3e1dc0" size="2">想向各位高手请教,我需要先用Hlookup查找到相应部门(如下: operation,finance,commercial),然后求该部门2008和2009数据和,用sum嵌套吗?该怎么设置公式,多谢!</font></p><p><table cellspacing="0" cellpadding="0" width="338" border="0" style="WIDTH: 254pt; BORDER-COLLAPSE: collapse;"><colgroup><font color="#3e1dc0"><col width="72" style="WIDTH: 54pt;"></col><col width="86" style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2752;"></col><col width="88" style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 2816;"></col><col width="92" style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 2944;"></col></font></colgroup><tbody><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl22" width="72" height="17" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">　</font></td><td class="xl22" width="86" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">Operation</font></td><td class="xl22" width="88" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">Finance</font></td><td class="xl22" width="92" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 69pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">Commercial</font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl22" align="right" height="17" 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;"><font face="Times New Roman" color="#3e1dc0" size="2">2007</font></td><td class="xl22" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">*****</font></td><td class="xl22" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">*****</font></td><td class="xl22" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">*****</font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl22" align="right" height="17" 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;"><font face="Times New Roman" color="#3e1dc0" size="2">2008</font></td><td class="xl22" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">*****</font></td><td class="xl22" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">*****</font></td><td class="xl22" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">*****</font></td></tr><tr height="17" style="HEIGHT: 12.75pt;"><td class="xl22" align="right" height="17" 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;"><font face="Times New Roman" color="#3e1dc0" size="2">2009</font></td><td class="xl22" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">*****</font></td><td class="xl22" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">*****</font></td><td class="xl22" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Times New Roman" color="#3e1dc0" size="2">*****</font></td></tr></tbody></table></p><p><font color="#3e1dc0"></font></p><p></p>

山菊花

<p>=SUM(HLOOKUP(?部门,B1:C4,{3;4},))</p><p><font color="#aaaaaa">数组公式，按Ctrl+Shift+Enter结束。</font></p><p>=SUMproduct(HLOOKUP((?部门,B1:C4,{3;4},))</p><p>=SUMPRODUCT((B1:D1=部门)*B3:D4)</p>

HLEx

<p><font face="Times New Roman" color="#3e18d7" size="2">明白了,多谢帮忙!对数组和sumproduct还一知半解, 还要继续努力，多谢！</font></p>

smilejs

高深