用SUMPRODUCT和COUNTIF函數,輕鬆實現多區間取值

2021-03-02 財會人修煉社

記得去年有個會計朋友向我請教一個函數問題，因為當時工作非常忙，再加上那時對函數沒怎麼去學習，我在工作中一般都是用VBA+ASP來提高工作效率，用函數也是一些常用的函數應用，最近有空了，想研究下函數技巧，於是便想到了這個函數問題，類似於我公司計算銷售提成點的問題。如圖：

黃色區域裡是銷售提成點，c2:e2區域表示每達到一個金額階段，提成點就不一樣，g6,h6,i6分別對應三個條件，國家，銷售類型，銷售金額，要根據左邊A3:E8區間的數據找出滿足三個條件的對應提成點。

這個問題要怎麼做呢，在j6寫入函數：=SUMPRODUCT((G6=$A$3:$A$8)*(H6=$B$3:$B$8)*COUNTIF(I6,$C$2:$E$2)*$C$3:$E$8)

如下圖：

SUMPRODUCT是個多條件求和函數。

G6=$A$3:$A$8 是第一個條件，對應國家

H6=$B$3:$B$8 是第二個條件，對應銷售類型

COUNTIF(I6,$C$2:$E$2) 是第三個條件，對應銷售金額

$C$3:$E$8 是求和區域

第三個條件，函數COUNTIF寫法有點特別。

COUNTIF函數語法是：

=countif（range，criteria）

參數意義：

參數range：表示要計算其中滿足條件的非空單元格數目的單元格區域；

參數criteria：表示統計條件，其條件的形式可以為數字、表達式或文本，也可以使用通配符。

函數的通俗表達方式：

=countif(數據區域，條件表達式)

這裡的數據區域就是I6,也就是金額28000，條件表達式是個區域，包含三個條件表達式，分別是「<10000","<=30000",">30000",這個函數相當於三個函數：

=countif(28000,"<10000");

=countif(28000,"<=30000");

=countif(28000,">30000");

上面的函數運算結果相當於生成了一個數組={0,1,0},可以在公式編輯欄，選中COUNTIF(I6,$C$2:$E$2),再按下F9鍵，就可以看到運行生成的結果。按CTRL+Z鍵取消公式運算便可以返回原公式。

你看明白了嗎，動手操作，才是王道~~

如果你是新同學，長按下面二維碼 - 識別圖中二維碼 - 關注，就可以和我一起學Excel技術了。

相關焦點

  • Excel必學的sumproduct函數,全部9種用法都在這
    函數，它可以實現求和、單一多條件和複雜情況下的各類計數及綜合排名等數據處理，今天我們就來學習一下這個函數的全部9種用法。函數2=SUMPRODUCT(D3:D8*E3:E8)案例講解：sumproduct函數將相應元素之間通過相乘並求和計算，可以用逗號或是用*號進行連接，返回相應的數組或區域乘積的和。
  • Sumproduct函數頂多個條件函數,四個操作讓你知道它有多強
    :C8=I5))函數解析：sumproduct函數在多條件計算的時候，只需要將多個條件值用*進行連接即可。H5)*(C1:C8=I5),E1:E8)函數解析：sumproduct函數在多條件求和的時候，第一參數(B1:B8=H5)*(C1:C8=I5)可以將多個條件值用*號進行連接，第二參數E1:E8為對應的求和區域即可。
  • EXCEL--多條件取值終極解法SUMPRODUCT高級應用
    ，今天又有人問了一個a<A<=b 及大於較小值而小於等於較大值的多條件 那個公式在節點上就判斷不準了，我又實在不想用if函數去做，嵌套一大堆得if 看著都累，先後試驗了vlookup，Hlookup，match，發現它們的內在原理都是一樣的 沒辦法只好請出SUMPRODUCT大神了實例如下輸入500>=a1>0 的數的時候，B1顯示為A1*0.1
  • Excel-分段函數取值之巧用INDEX+MATCH
    Excel中常規的做法，使用IF函數嵌套實現，就像下圖，判斷是「大於等於60%」還是「小於60%」。上面的公式，由於只有兩個區間，所以，一個IF函數就可以實現，很簡單。稍複雜一點，變為三個區間，如下：此時，需要兩個IF語句，嵌套實現。
  • Countif函數24種公式設置方法,幫你整理全了
    假空單元格數量=COUNTIF(A2:A32,"")-COUNTIF(A2:A32,"=")真空+假空-真空=假空9、 統計A列身份證號410105198504182965出現的數量=COUNTIF（A:A，"410105198504182165*"）默認情況下，countif函數對數字只識別前15位，而帶上通配符*後可以識別其為文本型格式
  • sumproduct函數用法全匯總,輕鬆玩轉綜合權重排名
    平時我們在計算按條件計算的時候，用到的函數通常有sumif、averageif等等條件函數。今天我們來學習一個經典的sumproduct函數。
  • 利用SUMPRODUCT函數進行多條件求和
    sumproduct函數是一個數組函數，用於計算幾組數組間對應元素乘積之和。
  • 解析:sumproduct多條件求和計數的原理
    例：如下圖所示，要求在C10單元格根據「產品」和「型號」兩個條件，統計銷售總數量。   公式：      =SUMPRODUCT((A3:A7=A10)*(B3:B7=B10)*C3:C7)   公式解析:      1 首先我們先拋開sumproduct函數，看看括號內是怎麼運算的。
  • 8種sumproduct函數的使用方法,除了強大,我不知道說什麼了
    今天要給大家介紹下Excel中的「萬能公式」sumproduct函數，為什麼說他是萬能的呢，因為它能做的事情是實在多了，廢話不多說我們開始把sumproduct函數以及參數sumproduct函數：返回相應的數組或區域乘積的和第一參數：Array1第二參數：array2第三參數：array3,…….最多
  • SUMPRODUCT函數三大經典案例
    由此可知，這是一道單條件計數問題，通常我們都是用countif函數，那如何運用Sumproduct單條件計數呢，如下所示:0處理，故此時我們需要將其在後方 *1 ，將邏輯值轉化為數值，則為{0;1;0;0;0;1}，然後SUMPRODUCT函數計算其乘積和，結果為2。
  • SUMPRODUCT多條件求和為什麼會出錯?
    sumproduct函數竟然不支持通配符如果你看了上面的兩篇文章，你就知道SUMPRODUCT函數是多條件統計和求和的萬能函數，常用的方法是多條件統計：SUMPRODCUT((條件1)*(條件2)*(條件3)….
  • Countif函數你真的會用嗎?五個操作帶你輕鬆玩轉Countif
    可能大家都知道這個函數可以實現單一條件計算，實際上它在操作過程中還有許多細節的操作。今天我們就通過下面的案例來完整的學習一下這個計數函數。函數公式：男=COUNTIF(C:C,G4)=COUNTIF(C:C,"男")女=COUNTIF(C:C,G5) =COUNTIF(C:C,"女")  函數解析：1、countif函數為兩個參數，第一參數為條件區域，第二參數為需要計數的條件值；2、在條件計算的時候
  • Excel文本函數find和findb教程
    在眾多的文本函數中，find無疑是必會函數之一，除了它自身的定位功能外，還經常在工作中與其他函數搭配使用，使其應用更為廣泛。為了讓大家多了解一些find函數的用法，我專門整理了這期教程。對於教程有任何想法和需求，可以在底部留言給我，集中的問題我會專門寫教程針對性解決。
  • 多工作表跨表按條件匯總求和
    微信公眾號 | Excel函數與公式（ID：ExcelLiRui）個人微信號 | （ID：ExcelLiRui520）多工作表跨表按條件匯總求和之前在文章中講過條件求和的常用方法，單個工作表的條件求和，很多同學都已經會藉助Excel自動計算了，但當遇到多表條件匯總的問題，很多人還沒有思路。
  • Excel多區間判斷別在使用IF函數了,這個函數更簡單!
    導讀：IF函數是Excel中最基礎的邏輯函數，相信大家都非常熟悉，近期學習社群中有很多小夥伴分享了不少「喪心病狂」的IF多層嵌套函數，嵌套了七八層IF函數，那麼針對這種IF多區間嵌套函數，有什麼可以優化的地方呢？文/芒種學院@指北針（ID：lazy_info）Hi，大家早上好，這裡是芒種學院。
  • 【Excel函數教程】史上最弱的FREQUENCY函數教程,按區間計數捨我其誰!
    FREQUENCY 函數計算值在某個範圍內出現的頻率，然後返回一個垂直的數字數組。例如，使用函數 FREQUENCY 可以在分數區域內計算測驗分數的個數。由於 FREQUENCY 返回一個數組，所以它必須以數組公式的形式輸入。
  • 多工作簿單工作表多表合併
    點擊上方藍字關注 Excel函數與公式關注後發送函數名稱，即可獲取對應教程原創作者 | 李銳
  • Excel中的aggregate函數,你會用嗎?
    （ID：ExcelLiRui）微信個人號 | （ID：ExcelLiRui520）關鍵字：aggregateExcel中的aggregate函數，你會用嗎？aggregate函數是一個功能相當豐富和強大的函數，集眾多功能於一身，求和、計數、求平均、最大值、最小值等等樣樣都會，學過subtotal函數的同學一定會想到subtotal也有這樣的功能。但是今天出場的aggregate函數比subtotal函數還要強大，因為面對錯誤值和分類匯總嵌套時subtotal無法處理，但aggregate照樣搞的定！
  • Sumproduct_條件求和(多_可且可或)_Excel
    我們的A系列已經結束，錯過A系列沒有關係，A系列基本都是講技巧，A系列全套示例文件(196MB)已經全部開放，下載收藏就行，做到了解即可。Sumproduct_條件求和（多_可且可或）多條件求和函數類區函數格式（官方）函數格式（中文）Sumproduct數學SUMPRODUCT(array1,array2,array3,…)SUMPRODUCT(數組1,數組2,數組3,…)今天不上動圖Sumproduct_條件求和（多_純且）注意：如果不是引用單元格
  • 別怕,其實SUMPRODUCT函數很簡單!
    《別怕，其實Excel函數很簡單！》《別怕，其實OFFSET函數很簡單！》這是Excel100天學習班自定義學習內容，讀者對條件求和、條件計數相關函數感到很疑惑。SUMIF、SUMIFS、COUNTIF、COUNTIFS和SUMPRODUCT函數，那麼多，一下子用這個，一下子用那個，頭都大。