2009/08/24

Excel多條件(變數)countif函數sumproduct

如果你曾使用過Excel的countif函數,你就會像我一樣發現不論用or/and怎麼設定,就是不能count超過二個以上的條件(變數)。

前幾天,我實在太不爽了,就上網用「2 variable countif」搜尋,果然讓我發現一個解決方案:sumproduct。

如果


在 Excel 內按 F1 後,再輸入「SUMPRODUCT」去找相關的資訊,就會發現以下的畫面:


沒錯!如果你讀了裏面的內容,就會發現這個例子和 countif 沒什麼關係,所以看了也沒用!所以我就直接依照 countif 的需要去試 SUMPRODUCT:
=SUMPRODUCT((range="animal")*(range>0))
果然讓我試出 countif 的功能!

實例


以下面的例子來說:


如果要算出「E2」欄位:總計Sales部門內所有進度等於A的人數,就要輸入:
=SUMPRODUCT((B2:B11="Sales")*(C2:C11="A"))


注意事項


  1. 如果你想要在 Google Spreadsheet 內使用 SUMPRODUCT 這個函數,必須在前面加入ARRAYFORMULA函數,就能正常使用了。例如:=ARRAYFORMULA(SUMPRODUCT((B2:B11="Sales")*(C2:C11="A")))
  2. 另外," "內不可以有多餘的空白,如果寫成"Sales "就會得到 #VALUE! 錯誤訊息。

  1. mrexcel -《COUNTIF with two Variables?
  2. Google 網上論壇-《SUMPRODUCT won't work

21 則留言:

  1. 從Excel 2007開始就提供countifs的函數可以設多重條件了,缺點就是要錢。

    回覆刪除
  2. Hi 光:
    謝謝你的回應。雖然Excel 2007已經有這個功能了,但我想大多數的人短期內都不會因為只想要使用這個功能而買Excel 2007。
    另外,這個功能在Google 試算表內也可以使用,但要修改一下公式才行!這個部份我已經加在文末了。

    回覆刪除
  3. 好用,解了我的問題。
    太感謝了。

    回覆刪除
  4. To 3樓的匿名:
    不客氣,很高興我寫的文章對你是有幫助的。
    如果有相關的問題,有歡迎提問!

    回覆刪除
  5. 真厲害!!解決了困擾我很久的問題!!>"<

    回覆刪除
  6. Hi,最近我也在試countif,找到網路上有人分享正確的用法,跟您分享一下: http://wilddog125.blogspot.com/2008/09/excel-countif.html

    thanks.

    回覆刪除
  7. To 6樓的匿名:
    很高興你對我寫的文章有興趣,不過,你可以告訴我文章內有那個地方是「不正確」的?
    我怎麼找就是找不到呢,可以請你幫忙嗎?

    回覆刪除
  8. 謝謝分享唷!很受用^^

    回覆刪除
  9. 謝謝您的文章, 解決了我的問題

    COUNTIF 雖然可以給動態條件, 可是仍然不能跨兩個欄位下條件, 用您的方式, 是可以下多個不同欄位的條件呢

    六樓的解法就是 Office 2007 的新函數, 不過, 不是每個人都用 2007 阿~~~~

    回覆刪除
  10. 我找了好久,終於找到在google document上面多條件式的加總方法,太感謝了。

    回覆刪除
  11. 謝謝你!!~這個方法太方便了

    回覆刪除
  12. 可是好像不能用在數字耶!例如:找出C欄中大於150,且D欄中小於15;這種的就不行

    回覆刪除
  13. Hi 文禎:
    我在Google Doc內已經試過,確定是可以用這個方法判斷range的。
    所以我懷疑你是不是把判斷的數字給括"起來了?""是指文字,如果是比較數字就不需要"",所以你要不要再試一下?

    回覆刪除
  14. mybaye2003@yahoo.com.tw2011/7/12 下午4:37

    您好!我有EXCEL的問題可以請教您嗎?我想設多重函數,但我不會設,我的東西放在http://www.funp.net/704529
    可以幫我嗎?我的E-Mail是mybaye2003@yahoo.com.tw,拜託您!我需要各別統計業務的資料,例如:JASON開案件數,開案件數合計金額,已開發票金額統計,已開發票未應款金額統計,這都就每個業務個人狀況來統計,請幫幫忙,謝!

    回覆刪除
  15. Hi mybaye2003@yahoo.com.tw,
    從Excel 2007開始就提供countifs的函數可以設多重條件,我看你的檔案.xlsx 應該已經使用Excel 2007了,所以你就用F1 找countifs的函數就能參考實際的案例使用。
    另外,自己的工作還是要自己做啦,不要找到人就要求別人幫忙啦!

    回覆刪除
  16. OH~~~YA~~~
    謝謝幫大忙了說~

    回覆刪除
  17. 超棒!!給你十個讚!!!

    回覆刪除
  18. 太感謝了!!!!感謝分享!!!!
    試了好久的GDocs終於看到這一篇而成功!!!

    回覆刪除
  19. 請問可跨工作表嗎? 我是用2003 EXCEL
    但卻是出現#N/A,說公式或函數無法使用某值.Q_Q

    回覆刪除
  20. Hi 蘇小婷:
    不懂你的意思,你可以用實際的例子說明嗎?另外,我也沒有Office2003可以試用。

    回覆刪除
  21. 啊啊~ 我懷疑是同事給我的報表有問題!!

    不然我試您的方法的確可以耶~ 囧
    我得再多試幾次了.

    感謝通達人超快就回覆!!呵呵~ :D

    回覆刪除