Excel 想請教有顏色的儲存格用何函數較能正確快速的計算出結果呢？ COUNTIFS?

試試: 

C3:=round(sumproduct((F9:F29="北一區")*(I9:I29="百貨類")*(E9:E29>=2018/1/1)*(E9:E29<=2018/1/31)/IF(COUNTIFS(B9:B29,B9:B29,F9:F29,"北一區",I9:I29,"百貨類",E9:E29,">=2018/1/1",E9:E29,"<=2018/1/31")>0,COUNTIFS(B9:B29,B9:B29,F9:F29,"北一區",I9:I29,"百貨類",E9:E29,">=2018/1/1",E9:E29,"<=2018/1/31"),999999)),0)

說明:
1. 資料範圍這裡只假設到第29列, 實際請依你的資
    料修正.
2. COUNTIFS 函數計算符合條件之各分店出現次數
    算出. 此將做為分母, 因此如果不符條件則會是 0,
    用大數 999999 代入既避免除以 0 的錯誤, 而且對
   最後結果的影 響盡可能降低 (除非你的資料很龐大).
3. SUMPRODUCT 函數將符合條件的分店做加總.
    只出現1次就算1, 出現2次就是1/2+1/2, 出現3次就
    是1/3+1/3+1/3, 以此類推. 不符條件的是 1/999999
    的合計.
4. 最外層的 ROUND 函數一是修正如 1/3+1/3+1/3
    之類的浮點運算誤差, 二是消除那些 1/999999 的
    作用.

C4:=round(sumproduct((F9:F29="北一區")*(I9:I29="3C類")*(E9:E29>=2018/1/1)*(E9:E29<=2018/1/31)/IF(COUNTIFS(B9:B29,B9:B29,F9:F29,"北一區",I9:I29,"3C類",E9:E29,">=2018/1/1",E9:E29,"<=2018/1/31")>0,COUNTIFS(B9:B29,B9:B29,F9:F29,"北一區",I9:I29,"3C類",E9:E29,">=2018/1/1",E9:E29,"<=2018/1/31"),999999)),0)

C5:=round(sumproduct((F9:F29="北一區")*(I9:I29="清潔用品類")*(E9:E29>=2018/1/1)*(E9:E29<=2018/1/31)/IF(COUNTIFS(B9:B29,B9:B29,F9:F29,"北一區",I9:I29,"清潔用品類",E9:E29,">=2018/1/1",E9:E29,"<=2018/1/31")>0,COUNTIFS(B9:B29,B9:B29,F9:F29,"北一區",I9:I29,"清潔用品類",E9:E29,">=2018/1/1",E9:E29,"<=2018/1/31"),999999)),0)

C6:=round(sumproduct((F9:F29="北一區")*(I9:I29="電器類")*(E9:E29>=2018/1/1)*(E9:E29<=2018/1/31)/IF(COUNTIFS(B9:B29,B9:B29,F9:F29,"北一區",I9:I29,"電器類",E9:E29,">=2018/1/1",E9:E29,"<=2018/1/31")>0,COUNTIFS(B9:B29,B9:B29,F9:F29,"北一區",I9:I29,"電器類",E9:E29,">=2018/1/1",E9:E29,"<=2018/1/31"),999999)),0)

有請神人幫忙解救一下，嘗試很多次都沒能算出😭😭

C3鍵入
=COUNTIFS(F:F,+$F$15,I:I,+$I$11)即可
其他類推........
<說明>
"北一區"、"百貨類"直接以
"+$F$15"、"+$I$11" 取樣,
是因怕打字"字節"有出入!

有興趣,可找我!
圖片是對你情況的模擬!