錦子老師

謝小小：「錦子老師，如下表格，希望能在日期或是組別篩選後，在O欄的正負數字能各自加總。」

錦子老師：

1、點取O1儲存格輸入公式：

=SUMPRODUCT((SUBTOTAL(9,OFFSET(O4,ROW(O4:O23)-ROW(O4),)))*(O4:O23>0))

【公式解說】

(O4:O23>0) 傳回O4:O23儲存格中大於0的為TRUE(真,1)否則為FALSE(假,0)。

ROW(O4:O30)-ROW(O4) 傳回O4:30儲存格列號-O4儲存格列號令這是資料開始，判斷要抓多少列)

OFFSET(O4,ROW(O4:O30)-ROW(O4),) 傳回從O4儲存格移動多少列。

SUBTOTAL(9,OFFSET(O4,ROW(O4:O30)-ROW(O4),)) 計算OFFSET函數範圍內的總和

SUMPRODUCT((SUBTOTAL(9,OFFSET(O4,ROW(O4:O30)-ROW(O4),)))*(O4:O30>0)) 計算篩選結果的O欄與(O4:O23>0)對應儲存格的1(0)值相乘的總和。

2、點取O2儲存格輸入公式：

=SUMPRODUCT((SUBTOTAL(9,OFFSET(O4,ROW(O4:O23)-ROW(O4),)))*(O4:O23<0))

3、 點取C3儲存格右邊▼鈕，點取「21日」核取方塊，使其打勾，其餘空白。

4、點取「確定」鈕，自動換算篩選部份的O欄金額。

部落格相關範例