[엑셀] 필터 합계, 레코드갯수 함수 사용법 (SUBTOTAL,COUNTA)

<table id=cafe_editor_main_table_color bgColor='#d1b2ff' cellpadding=0 cellspacing=0 border=0 width=100%><tr><td id=cafe_editor_main_table class=text>  <table width="694" style="width: 521pt; border-collapse: collapse;" border="0" cellspacing="0" cellpadding="0"><colgroup><col width="309" style="width: 232pt; mso-width-source: userset; mso-width-alt: 9888;"><colgroup><col width="385" style="width: 289pt; mso-width-source: userset; mso-width-alt: 12320;"><tbody><tr height="243" style="height: 182.25pt; mso-height-source: userset;"><td width="694" height="243" class="xl64" style="border-width: 1pt; border-style: solid; border-color: windowtext black windowtext windowtext; width: 521pt; height: 182.25pt; background-color: lime;" colspan="2">Excel(엑셀) 필터 합계, 레코드수함수 사용법 (SUBTOTAL,COUNTA)</td></tr></tbody></table>   <table width="652" style="width: 490pt; border-collapse: collapse;" border="0" cellspacing="0" cellpadding="0"><colgroup><col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1696;"><colgroup><col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1280;"><colgroup><col width="72" style="width: 54pt;"><colgroup><col width="274" style="width: 206pt; mso-width-source: userset; mso-width-alt: 8768;"><colgroup><col width="213" style="width: 160pt; mso-width-source: userset; mso-width-alt: 6816;"><tbody><tr height="46" style="height: 34.5pt; mso-height-source: userset;"><td width="93" height="46" class="xl75" style="border-width: 1pt 0.5pt 2pt 1pt; border-style: solid solid double; border-color: windowtext; width: 70pt; height: 34.5pt; text-align: center; background-color: transparent;" colspan="2">함수</td><td width="72" class="xl76" style="border-width: 1pt 0.5pt 2pt 0px; border-style: solid solid double none; border-color: windowtext windowtext windowtext black; width: 54pt; text-align: center; background-color: transparent;">의미</td><td width="487" class="xl76" style="border-width: 1pt 1pt 2pt 0px; border-style: solid solid double none; border-color: windowtext black; width: 366pt; text-align: center; background-color: transparent;" colspan="2">사용법</td></tr><tr height="23" style="height: 17.25pt;"><td height="23" class="xl69" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 17.25pt; text-align: center; background-color: yellow;" colspan="2">SUBTOTAL</td><td class="xl67" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">준합계</td><td class="xl68" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">SUBTOTAL(function_num,ref1,[ref2],...)</td><td class="xl73" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">SUBTOTAL(함수번호, 범위)</td></tr><tr height="23" style="height: 17.25pt;"><td height="23" class="xl71" style="border-width: 0.5pt 0.5pt 1pt 1pt; border-style: solid; border-color: windowtext; height: 17.25pt; text-align: center; background-color: transparent;" colspan="2">SUM</td><td class="xl65" style="border-width: 0px 0.5pt 1pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">총합계</td><td class="xl66" style="border-width: 0px 0.5pt 1pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">SUM(number1,[number2],...)</td><td class="xl74" style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">SUM(범위)</td></tr></tbody></table> 위 표와 같이 SUM 함수는 범위내 합계를 구하는데 사용됩니다.SUBTOTAL은 범위내에서 조건에 맞는 합계를 구하는데 사용되는 함수입니다. 아래와 같이 일자별로 거래한 내역 자료를 예시로 설명해 보겠읍니다. 예문 엑셀 파일 받기 클릭~~~↓↓↓↓↓ <a href="javascript:checkVirus('grpid%3D1PJdV%26fldid%3D1WY6%26dataid%3D48%26fileid%3D6%26regdt%3D20150707102035')"><img src="https://t1.daumcdn.net/daumtop_deco/icon/icon.hanmail.net/editor/p_etc_s.gif" border="0" alt="첨부파일" class="vam"/> 필터합계,레코드갯수.zip</a>    <table width="535" style="width: 402pt; border-collapse: collapse;" border="0" cellspacing="0" cellpadding="0"><colgroup><col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 2880;"><colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3008;"><colgroup><col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3360;"><colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2560;"><colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2400;"><colgroup><col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 2912;"><tbody><tr height="18" style="height: 13.5pt;"><td width="90" height="18" class="xl65" style="border: 0.5pt solid windowtext; border-image: none; width: 68pt; height: 13.5pt; background-color: yellow;">거래일자</td><td width="94" class="xl65" style="border-width: 0.5pt 0.5pt 0.5pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext black; width: 71pt; background-color: yellow;">거래처명</td><td width="105" class="xl65" style="border-width: 0.5pt 0.5pt 0.5pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext black; width: 79pt; background-color: yellow;">사업자번호 </td><td width="80" class="xl66" style="border-width: 0.5pt 0.5pt 0.5pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext black; width: 60pt; background-color: rgb(102, 255, 255);"> 공급가액  </td><td width="75" class="xl67" style="border-width: 0.5pt 0.5pt 0.5pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext black; width: 56pt; background-color: rgb(255, 204, 153);"> 세액  </td><td width="91" class="xl68" style="border-width: 0.5pt 0.5pt 0.5pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext black; width: 68pt; background-color: rgb(0, 204, 255);"> 합계금액 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.01.20</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사002</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">130-22-22222</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      50,000 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      5,000 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        55,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.02.02</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사002</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">130-22-22222</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      75,455 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      7,545 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        83,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.02.24</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사004</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">118-82-44444</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      50,000 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      5,000 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        55,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.02.27</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사002</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">130-22-22222</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      71,818 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      7,182 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        79,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.03.23</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사004</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">118-82-44444</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      63,636 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      6,364 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        70,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.03.23</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사004</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">118-82-44444</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      63,636 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      6,364 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        70,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.04.01</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사002</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">130-22-22222</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      50,000 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      5,000 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        55,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.04.09</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사003</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">108-06-33333</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      40,000 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      4,000 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        44,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.04.22</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사003</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">108-06-33333</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      36,364 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      3,636 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        40,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.04.23</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사004</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">118-82-44444</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">     120,000 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">     12,000 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">       132,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.04.30</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사001</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">217-11-11111</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">     163,636 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">     16,364 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">       180,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.05.12</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사004</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">118-82-44444</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      50,000 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      5,000 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        55,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.05.28</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사004</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">118-82-44444</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      70,000 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      7,000 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        77,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.05.31</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사002</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">130-22-22222</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      90,000 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      9,000 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        99,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.06.03</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사004</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">118-82-44444</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">     109,091 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">     10,909 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">       120,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.06.29</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사004</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">118-82-44444</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">      50,000 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">      5,000 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">        55,000 </td></tr><tr height="18" style="height: 13.5pt;"><td height="18" class="xl70" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 13.5pt; background-color: transparent;">2015.06.30</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">회사002</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">130-22-22222</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(102, 255, 255);">     108,182 </td><td class="xl72" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 153);">     10,818 </td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(0, 204, 255);">       119,000 </td></tr></tbody></table> 위의 자료에서 총레코드(행) 건수와 총합계를 구할때는COUNT함수와 SUM함수를 이용합니다.  <img src="https://t1.daumcdn.net/cfile/cafe/216AF93A559B2F853C" class="txc-image" width="677" height="735" style="width: 677px; height: 735px; clear: none; float: none;" border="0" vspace="1" hspace="1" actualwidth="872" exif="{}" data-filename="필터합계,레코드갯수01.jpg" id="A_216AF93A559B2F853C42E1"/> COUNTA(B2:B18)는 셀B2 부터 셀B18 까지의 문자셀 갯수를 구하고자 할때 쓰는 함수입니다.SUM(D2:D20)은 셀D2 부터 셀D20 까지의 합계를 구하고자 할때 쓰는 함수입니다. 위의 자료에서 "회사002"의 자료건수와 부분합계를 구할때는 어떻게 할까요?이때 필터링을 하고 SUBTOTAL함수를 사용합니다.  <table width="533" style="width: 400pt; border-collapse: collapse;" border="0" cellspacing="0" cellpadding="0"><colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2400;"><colgroup><col width="72" style="width: 54pt;"><colgroup><col width="136" style="width: 102pt; mso-width-source: userset; mso-width-alt: 4352;"><colgroup><col width="250" style="width: 188pt; mso-width-source: userset; mso-width-alt: 8000;"><tbody><tr height="57" style="height: 42.75pt; mso-height-source: userset;"><td width="533" height="57" class="xl86" style="border-width: 1pt; border-style: solid; border-color: windowtext black windowtext windowtext; width: 400pt; height: 42.75pt; text-align: center; background-color: yellow;" colspan="4">SUBTOTAL</td></tr><tr height="22" style="height: 16.5pt;"><td height="22" class="xl80" style="border-width: 1pt 0.5pt 0.5pt 1pt; border-style: solid; border-color: windowtext; height: 16.5pt; text-align: center; background-color: yellow;" colspan="2">함수번호</td><td class="xl82" style="border-width: 0px 0.5pt 2pt; border-style: none solid double; border-color: black windowtext; text-align: center; background-color: yellow;" rowspan="2">함수</td><td class="xl84" style="border-width: 0px 1pt 2pt 0.5pt; border-style: none solid double; border-color: black windowtext; text-align: center; background-color: yellow;" rowspan="2">의미</td></tr><tr height="23" style="height: 17.25pt;"><td height="23" class="xl78" style="border-width: 0px 0.5pt 2pt 1pt; border-style: none solid double; border-color: black windowtext windowtext; height: 17.25pt; text-align: center; background-color: rgb(255, 192, 0);">숨김 포함</td><td class="xl79" style="border-width: 0px 0.5pt 2pt 0px; border-style: none solid double none; border-color: black windowtext windowtext black; text-align: center; background-color: rgb(255, 204, 255);">숨김 제외</td></tr><tr height="36" style="height: 27pt;"><td height="36" class="xl65" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 27pt; background-color: rgb(255, 192, 0);">1</td><td class="xl66" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">101</td><td class="xl67" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">AVERAGE</td><td class="xl68" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">평균</td></tr><tr height="35" style="height: 26.25pt;"><td height="35" class="xl69" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 26.25pt; background-color: rgb(255, 192, 0);">2</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">102</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">COUNT</td><td class="xl72" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">숫자셀 갯수</td></tr><tr height="35" style="height: 26.25pt;"><td height="35" class="xl69" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 26.25pt; background-color: rgb(255, 192, 0);">3</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">103</td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: red windowtext windowtext red; background-color: transparent;">COUNTA</td><td class="xl72" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">문자셀 갯수</td></tr><tr height="35" style="height: 26.25pt;"><td height="35" class="xl69" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 26.25pt; background-color: rgb(255, 192, 0);">4</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">104</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">MAX</td><td class="xl72" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">최대값</td></tr><tr height="35" style="height: 26.25pt;"><td height="35" class="xl69" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 26.25pt; background-color: rgb(255, 192, 0);">5</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">105</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">MIN</td><td class="xl72" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">최소값</td></tr><tr height="35" style="height: 26.25pt;"><td height="35" class="xl69" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 26.25pt; background-color: rgb(255, 192, 0);">6</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">106</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">PRODUCT</td><td class="xl72" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">곱셈</td></tr><tr height="35" style="height: 26.25pt;"><td height="35" class="xl69" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 26.25pt; background-color: rgb(255, 192, 0);">7</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">107</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">STDEV</td><td class="xl72" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">부분집단 표준편차</td></tr><tr height="35" style="height: 26.25pt;"><td height="35" class="xl69" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 26.25pt; background-color: rgb(255, 192, 0);">8</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">108</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">STDEVP</td><td class="xl72" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">전체집단 표준편차</td></tr><tr height="35" style="height: 26.25pt;"><td height="35" class="xl69" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 26.25pt; background-color: rgb(255, 192, 0);">9</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">109</td><td class="xl73" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: red windowtext windowtext red; background-color: transparent;">SUM</td><td class="xl72" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">합계</td></tr><tr height="35" style="height: 26.25pt;"><td height="35" class="xl69" style="border-width: 0px 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 26.25pt; background-color: rgb(255, 192, 0);">10</td><td class="xl70" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">110</td><td class="xl71" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">VAR</td><td class="xl72" style="border-width: 0px 1pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">부분집단 분산</td></tr><tr height="36" style="height: 27pt;"><td height="36" class="xl74" style="border-width: 0px 0.5pt 1pt 1pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 27pt; background-color: rgb(255, 192, 0);">11</td><td class="xl75" style="border-width: 0px 0.5pt 1pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: rgb(255, 204, 255);">111</td><td class="xl76" style="border-width: 0px 0.5pt 1pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">VARP</td><td class="xl77" style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">전체집단 분산</td></tr></tbody></table> 함수번호가 백단위인 경우 숨김셀을 제외하고 함수적용하며일십단위인 경우는 범위내 숨김셀도 포함하여 함수적용 합니다.  <img src="https://t1.daumcdn.net/cfile/cafe/22037A38559B37A922" class="txc-image" width="663" height="778" style="width: 663px; height: 778px; clear: none; float: none;" border="0" vspace="1" hspace="1" actualwidth="864" exif="{}" data-filename="필터합계,레코드갯수02.jpg" id="A_22037A38559B37A922B248"/>우선 위자료와 같이 필터를 합니다.① 필터조건를 행을 선택후② "테이터" 메뉴 선택후③ "필터"메뉴를 선택 합니다.   <img src="https://t1.daumcdn.net/cfile/cafe/216BC438559B37AC2D" class="txc-image" width="660" height="730" style="width: 660px; height: 730px; clear: none; float: none;" border="0" vspace="1" hspace="1" actualwidth="960" exif="{}" data-filename="필터합계,레코드갯수03.jpg" id="A_216BC438559B37AC2DC36D"/>④ 셀 B2 부터 셀 B18까지의 "필터"내 숨김셀을 포함한 문자셀 갯수를 구하고자SUBTOTAL(3,B2:B18)를 입력 사용합니다. ⑤ 셀 D2 부터 셀 D18까지의 "필터"내 숨김셀을 포함한 부분합계를 구하고자SUBTOTAL(9,D2:D18)를 입력 사용합니다.    <img src="https://t1.daumcdn.net/cfile/cafe/247FAC38559B37B022" class="txc-image" width="666" height="769" style="width: 666px; height: 769px; clear: none; float: none;" border="0" vspace="1" hspace="1" actualwidth="960" exif="{}" data-filename="필터합계,레코드갯수04.jpg" id="A_247FAC38559B37B022C3F9"/> ⑥ 거래처셀의 ▼를 클릭 합니다.  ⑦ 검색조건 창에서 "회사002"를 체크선택후⑧ "확인" 클릭 합니다.    <img src="https://t1.daumcdn.net/cfile/cafe/21670B38559B37B430" class="txc-image" width="667" height="732" style="width: 667px; height: 732px; clear: none; float: none;" border="0" vspace="1" hspace="1" actualwidth="960" exif="{}" data-filename="필터합계,레코드갯수05.jpg" id="A_21670B38559B37B430AC74"/>⑨ 구하고자 하는 "회사002"의 거래건수와 합계가 나타납니다.  <div class="content_plusmap" data-mapdata="map_type=TYPE_SKYVIEW&map_hybrid=true&idx=1&title=%EC%9E%89%ED%81%AC%EC%8A%A4%ED%85%8C%EC%9D%B4%EC%85%98%20%EB%8C%80%EB%A6%BC%EC%A0%90&addr=%EC%84%9C%EC%9A%B8%20%EC%98%81%EB%93%B1%ED%8F%AC%EA%B5%AC%20%EB%8C%80%EB%A6%BC%EB%8F%99%20863-11&tel=02-848-8219&mapX=478655&mapY=1109163&ifrW=676px&ifrH=300px&addtype=1&map_level=6&rcode=1119070&docid=&confirmid=11169820&mapWidth=676&mapHeight=300&mapInfo=%7B%22version%22%3A2%2C%22mapWidth%22%3A676%2C%22mapHeight%22%3A300%2C%22mapCenterX%22%3A478655%2C%22mapCenterY%22%3A1109163%2C%22mapLevel%22%3A6%2C%22coordinate%22%3A%22wcongnamul%22%2C%22markInfo%22%3A%5B%7B%22markerType%22%3A%22standPlace%22%2C%22coordinate%22%3A%22wcongnamul%22%2C%22x%22%3A478658%2C%22y%22%3A1109165%2C%22clickable%22%3Atrue%2C%22draggable%22%3Atrue%2C%22icon%22%3A%7B%22width%22%3A35%2C%22height%22%3A56%2C%22offsetX%22%3A17%2C%22offsetY%22%3A56%2C%22src%22%3A%22http%3A%2F%2Fi1.daumcdn.net%2Flocalimg%2Flocalimages%2F07%2F2012%2Fattach%2Fpc_img%2Fico_marker2_150331.png%22%7D%2C%22content%22%3A%22%EC%9E%89%ED%81%AC%EC%8A%A4%ED%85%8C%EC%9D%B4%EC%85%98%20%EB%8C%80%EB%A6%BC%EC%A0%90%22%2C%22confirmid%22%3A11169820%7D%5D%2C%22graphicInfo%22%3A%5B%5D%2C%22roadviewInfo%22%3A%5B%7B%22width%22%3A676%2C%22height%22%3A200%2C%22panoX%22%3A%22478635%22%2C%22panoY%22%3A%221109203%22%2C%22pan%22%3A%22138.17682027651404%22%2C%22tilt%22%3A%222.952949631049516%22%2C%22zoom%22%3A%222%22%2C%22panoId%22%3A%221047485794%22%2C%22storeId%22%3Anull%2C%22storePanoId%22%3Anull%2C%22storePan%22%3Anull%2C%22storeTilt%22%3Anull%2C%22storeZoom%22%3Anull%2C%22storePanoX%22%3Anull%2C%22storePanoY%22%3Anull%2C%22startStore%22%3Afalse%2C%22checkRoadView%22%3Atrue%2C%22checkStoreView%22%3Afalse%7D%5D%7D&toJSONString="></div> <table width="693" style="width: 520pt; border-collapse: collapse;" border="0" cellspacing="0" cellpadding="0"><colgroup><col width="211" style="width: 158pt; mso-width-source: userset; mso-width-alt: 6752;"><colgroup><col width="482" style="width: 362pt; mso-width-source: userset; mso-width-alt: 15424;"><tbody><tr height="61" style="height: 45.75pt; mso-height-source: userset;"><td width="693" height="61" class="xl65" style="border-width: 1pt; border-style: solid; border-color: windowtext black windowtext windowtext; width: 520pt; height: 45.75pt; text-align: center; background-color: blue;" colspan="2">위사진에서 길찾기 하시면 도움이 됩니다. ^^*</td></tr><tr height="91" style="height: 68.25pt; mso-height-source: userset;"><td width="693" height="91" class="xl67" style="border-width: 1pt; border-style: solid; border-color: windowtext black windowtext windowtext; width: 520pt; height: 68.25pt; text-align: center; background-color: rgb(255, 153, 255);" colspan="2">   문제가 지속되면       전문가에게 도움을 요청하십시요.^^*</td></tr></tbody></table> <embed width="300" height="45" id="sallyWMP" src="https://t1.daumcdn.net/cfile/cafe/17676E3A4F1F5A5237" type="application/x-mplayer2" showstatusbar="false" showdisplay="false" showcontrols="true" showaudiocontrols="true" showpositioncontrols="true" showtracker="true" autostart="true" loop="-1" playcount="0" volume="70" allowNetworking='internal' allowScriptAccess='sameDomain'> Edit :  2015-07-07Edit :  2016-01-14 </tr></td></table>