假設column A 全行都是apple同pear, column B 全行都是small 同big, 想計下column A出現apple而columnB出現small有幾個, 我用=SUMPRODUCT((A:A="apple")*(B:B="small")), 但結果出現#NUM!, 請指正.
註:因為個FILE要不停加野,所以唔想SET死A1:A100,所以想set全個column
Excel的SUMPRODUCT問題
2010-10-27 7:56 pm
回答 (3)
2010-10-27 9:38 pm
✔ 最佳答案
用countif 或dcountaCOUNTIF -計算符合指定條件的區域中的非空儲存格數
=COUNTIF(RANGE,CRITERIA)
=DCOUNTA(DATABASE, FIELD, CRITERIA)
按照某一個指定的資料庫和准則，計算非空儲存格
http://hk.myblog.yahoo.com/jyhkmail-freelance
2010-10-29 7:45 am
You can use the offset formula to create a dynamic range first
then apply the name to the sumproduct formula,
detail and example file to go below link to download
http://sites.google.com/site/exceltkk2/excel-tutorial/howtocreatedynamicrange-example1
then apply the name to the sumproduct formula,
detail and example file to go below link to download
http://sites.google.com/site/exceltkk2/excel-tutorial/howtocreatedynamicrange-example1
2010-10-29 5:58 am
sumproduct 不可全欄引用, 陣列必須有固定長度, 如:
=SUMPRODUCT((A1:A100="apple")*(B1:B100="small"))
=SUMPRODUCT((A1:A100="apple")*(B1:B100="small"))
收錄日期: 2021-04-19 23:28:14
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20101027000051KK00369