EXCEL如何計算甲在8月共送貨幾家？

甲送幾家店? 
=sumproduct((E2:E25="甲")/countifs(A2:A25,A2:A25,E2:E25,E2:E25))

後面的 countifs 函數把 A, E 兩欄相同的列數算出來,
sumproduct 函數則把屬於 "甲" 的店加總. 例如甲送
A, C, D, G 店依次是 2, 2, 1, 2 次, 則 sumproduct 
將計算 1/2+1/2+ 1/2+1/2 + 1/1 + 1/2+1/2 = 4.



甲於8月份送幾家店?
=round(sumproduct((E2:E25="甲")*(B2:B25>="2020/08/01")*(B2:B25<="2020/08/31")/if(countifs(B2:B25,">=2020/08/01",B2:B25,"<=2020/08/31",A2:A25,A2:A25,E2:E25,E2:E25)>0,countifs(B2:B25,">=2020/08/01",B2:B25,"<=2020/08/31",A2:A25,A2:A25,E2:E25,E2:E25),200000)),0)

後面 countifs 的部分改用更複雜的
if(countifs(...)>0.countifs(...),200000)
當除數, 是避免了 "除以0" 的錯誤. 200000 只是任意
設定的 "大數", 放在除數使其對結果影響 "很小".
而最外層加 round 函數, 修正浮點運算誤差.



甲於8月份送幾家 "豆類" 店?
=round(sumproduct((E2:E25="甲")*(B2:B25>="2020/08/01")*(B2:B25<="2020/08/31")*(F2:F25="豆類")/if(countifs(B2:B25,">=2020/08/01",B2:B25,"<=2020/08/31",F2:F25,"豆類",A2:A25,A2:A25,E2:E25,E2:E25)>0,countifs(B2:B25,">=2020/08/01",B2:B25,"<=2020/08/31",F2:F25,"豆類",A2:A25,A2:A25,E2:E25,E2:E25),200000)),0)

.........篩選E列，用你手指點算有多少個「甲」