錦子老師

成龍：「錦子老師您好，請教一下，我在明細工作表有一表格。

希望能在總表工作表寫公式找出明細表中符合「地區」與「類」兩個條件的3個月份的總金額，像是加總工作表1~3月符合在台北的類別A的金額，目前我的寫法是=IF(A2:A11="台北",SUMIF(B2:B11,$P3,C2:E11),0)，但無法計算出正確數字，不知道是哪裡需要修改，麻煩幫我想想，謝謝~~？麻煩解惑 ~ 感恩，謝謝！」

錦子老師：「COUNTIF只可算1欄資料，故算出的答案會變成只統計明細工作表中的C欄：

可以改成用SUMPRODUCT函數統計，操作方法如下：

步驟1：點取B3儲存格輸入公式：

=SUMPRODUCT((明細!$A$2:$A$11=INDIRECT("總表!R1C"&4*QUOTIENT(COLUMNS ($B$1:B1),5)+2,FALSE))*(明細!$B$2:$B$11=總表!$A3)*(INDIRECT("明細!R"&2&"C"&(3* COLUMNS($A$2:B2))-3&":R"&11&"C"&(3*COLUMNS($A$2:B2))-1,FALSE)))

再按CTRL+SHIFT+ENTER鍵完成陣列公式，並將公式複製到B3:E6儲存格。

步驟2：點取F3儲存格輸入公式：

=SUMPRODUCT((明細!$A$2:$A$11=INDIRECT("總表!R1C"&4*QUOTIENT(COLUMNS ($B$1:F1),5)+2,FALSE))*(明細!$B$2:$B$11=總表!$A3)*(INDIRECT("明細!R"&2&"C"&(3* COLUMNS($A$2:B2))-3&":R"&11&"C"&(3*COLUMNS($A$2:B2))-1,FALSE)))

再按CTRL+SHIFT+ENTER鍵完成陣列公式，並將公式複製到F3:I6儲存格。

【公式說明】

公式1：INDIRECT("明細!R"&2&"C"&(3* COLUMNS($A$2:B2))-3&":R"&11&"C"&(3* COLUMNS($A$2:B2))-1,FALSE)

傳回「明細工作表第二列第N(到目前儲存格欄數*3再減3)欄到第11列第N(到目前儲存格欄數*3再減1)欄的儲存格範圍，這是為了一次抓3個月資料。

公式2：QUOTIENT(COLUMNS ($B$1:F1),5)

將B1到目前欄位第1列的列數除以5，傳回整數部份值。

公式3：(明細!$A$2:$A$11=INDIRECT("總表!R1C"&4*公式2+2,FALSE))

傳回明細工作表A2:A11儲存格中與總表工作表第一列第N(4*公式2加2的值)欄儲存格內容是否相同，相同傳回TRUE，否則傳回FALSE，這是為了抓地區。

公式4：(明細!$B$2:$B$11=總表!$A3)

傳回明細工作表與總表A3儲存格是否相同，相同傳回TRUE，否則傳回FALSE，這是為了抓同類資料。

公式5：SUMPRODUCT(公式3*公式4*公式1)

傳回符合公式3、公式4與公式1的數值資料合計。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

更多相關影片教學：請點我

更多相關文章：請點我