前幾期的文章中給大家介紹了一個關於條件求和的例子,文章連線為:
這是一個簡單的條件求和的問題,但是SUMIF解決不了
,
這期是同樣的一個問題,只能條件稍微有變化,如下圖所示:
注意:與上次不同的是,上期的文章中的條件是“*月”,比如“1月”,而應收款日期列的格式是“yyyy/mm/dd”,而這次屬於上面的這種型別。對於這樣的情況,如果不進行處理,同樣SUMIF與SUMIFS函式也是沒有辦法進行統計的。
1
Sumproduct+Search
在G2單元格中輸入公式,按Enter鍵完成後向下填充:
=SUMPRODUCT((ISNUMBER(SEARCH(F3&"*",$B$2:$B$25))*$C$2:$C$25))
如果將上面的公式改寫成陣列公式,即在G2單元格中輸入公式,按鍵完成後向下填充:
=SUM((ISNUMBER(SEARCH(F3&"*",$B$2:$B$25))*$C$2:$C$25))
注:SEARCH函式支援萬用字元查詢,同時第一個引數也支援單元格區域;ISNUMBBER函式是將SEARCH函式查詢的結果進行判斷,是否為數字,如果為數字,返回TRUE,如果不是則返回為FALSE,同時邏輯值又可以與數值進行互換,所以就可以正確地得到結果。
2
Sumproduct+Left
在G2單元格中輸入公式,按Enter鍵完成後向下填充:
=SUMPRODUCT((--LEFT($B$2:$B$25,6)=F3)*$C$2:$C$25)
如果將上面的公式改寫成陣列公式,即在G2單元格中輸入公式,按鍵完成後向下填充:
{=SUM((--LEFT($B$2:$B$25,6)=F3)*$C$2:$C$25)}
注:這裡使用LEFT函式左取日期列6位,然後再使用“——”減負運算將文字型的數值轉換成與目標值一致的數值型的。
3
Sumproduct+Find
在G2單元格中輸入公式,按Enter鍵完成後向下填充:
=SUMPRODUCT((--LEFT($B$2:$B$25,6)=F3)*$C$2:$C$25)
如果將上面的公式改寫成陣列公式,即在G2單元格中輸入公式,按鍵完成後向下填充:
{=SUM((ISNUMBER(FIND(F3,$B$2:$B$25)))*$C$2:$C$25)}
注:FIND函式不支援萬用字元查詢。這裡使用FIND函式第一個引數支援單元格區域的特性,找目標值是否在日期列中能否找到,然後自用ISNUMBER判斷是否為數字,如果為數字,表示能找到,則返回TRUE,否則表示找不到,則返回FALSE,再根據邏輯值與數值的互換的原理去乘積求和。
undefined
undefined