大家好， 今天和大家分享“這題讓sumifs函數頭痛了"， 問題是這樣的“Sumifs按日期列的月份求和”， 一個學員的提問， 我本來是用陣列公式很簡單的實現了， 可是這位學員要求和我用sumifs來解， 如果用sumifs來解這個題就很牽強了， 因為sumifs日期列要是儲存格區域引用， 而現在條件並不是直接存在儲存格區域， 所以有點不好弄， 沒有辦法， 誰叫你是老師呢？， 學員提到了這個問題， 這就是我今天分享這文章的理由， 要求如下圖||更多Excel學習和問題請加群：289393114、570064677
一、解法1
1、公式截圖
2、公式
=SUMIFS(C2:C13,A2:A13,F2,B2:B13,">="&DATE(2017,E2,1),B2:B13,"<="&(DATE(2017,E2+1,1))-1)
3、公式解釋
date函數根據年， 月， 日 返回一個日期， 它3個參數， 第1參數年；第2參數月；第3參數日
sumifs這裡用了3個條件， 按題意是按2個條件求和， 第1個條件是按名稱；第2個條件是按日期的月份， 也就是說根據E2的名稱， F2的月份進行求和
由於B列是日期， 而條件又是月份， sumifs第1參數， 第2參數， 第4參數要求是儲存格區域引用， 不能是常量陣列， 所以我們這裡要換一個思路解決它， 如E2是8， 那麼我們把這個轉為2個條件， 大於等於2017-8-1， 而且小於等於2017-8-31， 如果每一個月都是31天就好了， 可是有的月份沒有31天， 所以這裡又要變通一下， 我們先找到9月份的第1天， 再減掉1天， 剛好就是8月份的最後一天， (DATE(2017,E2+1,1))-1， 當然這裡也有一個函數可以實現EOMONTH(DATE(2017,E2,1),0)是指本月的最後一天
也可以用這個公式=SUMIFS(C2:C13,A2:A13,F2,B2:B13,">="&DATE(2017,E2,1),B2:B13,"<="&EOMONTH(DATE(2017,E2+1,0),0))
二、解法2
1、公式截圖
2、公式
=SUM((A2:A13=F2)*(MONTH(B2:B13)=E2)*(C2:C13))
3、公式解釋
這是陣列公式
區域A2:A13滿足等於A， 而且區域B2:B13把月份提取出來是否等於8， 這個兩個條件都滿足再乘以數量儲存格區域2:C13， 最後用sum求和
這種解法相比sumifs來說就簡單了許多，
建議這種也透視表實現很方便 ， 在透視表裡把日期按月組合。 這裡我就不說具體的方法了。