學不完．教不停．用不盡

網友根據這篇文章：

Excel-在幣別+金額的清單中計算各種幣別的小計(SUMPRODUCT,LEFT,MID)

想要詢問他人做法，儲存格F2：

=SUMPRODUCT(COUNTIF(E2,LEFT($A$2:$A$9)&"*")*MID($A$2:$A$9,4,10))

其中 COUNTIF(E2,LEFT($A$2:$A$9)&"*") 的意義為何？

【公式解析】

此公式使用 SUMPRODUCT 函數，可以執行『陣列』的運算。

1. COUNTIF(E2,LEFT($A$2:$A$9)&"*")

公式相當於：=COUNTIF(E2,LEFT($A$2:$A$9,1)&"*")，其中參數１被省略了。

結果：=COUNTIF("NT$",{"N*","H*","N*","H*","N*","N*","H*","N*"})

結果：=COUNTIF({1,0,1,0,1,1,0,1})（第一個字是 Ｎ 者傳回 1，是 H 者傳回 0。）

2. MID($A$2:$A$9,4,10))

結果：=MID({"1247","647","1040","1514","397","926","1516","258"})

3. SUMPRODUCT(COUNTIF(E2,LEFT($A$2:$A$9)&"*")*MID($A$2:$A$9,4,10))

公式相當於：=SUMPRODUCT(COUNTIF({1,0,1,0,1,1,0,1})*MID({"1247","647",
"1040","1514","397","926","1516","258"})

結果：=SUMPRODUCT(1247,0,1040,0,397,926,0,258)

結果：=3868