如何使用Excel COUNTIFS函数统计不同数值区间的符合条件数据

COUNTIFS核心语法 #

COUNTIFS(条件区域1, 条件1, 条件区域2, 条件2, ...)
需按「条件区域+对应条件」的成对格式传入参数，只有同时满足所有条件的记录才会被计数。

针对你的场景的公式 #

假设Crowdfunding!$G:$G是状态列（取值为failed/passed/canceled），Crowdfunding!$D:$D是用于判断区间的数值列，以下是各区间统计**状态为passed**数据的公式：

• 小于1000：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, "<1000")
  

• 1000-4999（包含两端）：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=1000", Crowdfunding!$D:$D, "<=4999")
  

• 5000-9999（包含两端）：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=5000", Crowdfunding!$D:$D, "<=9999")
  

• 10000-14999（包含两端）：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=10000", Crowdfunding!$D:$D, "<=14999")
  

• 15000-19999（包含两端）：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=15000", Crowdfunding!$D:$D, "<=19999")
  

• 20000-24999（包含两端）：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=20000", Crowdfunding!$D:$D, "<=24999")
  

• 25000-29999（包含两端）：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=25000", Crowdfunding!$D:$D, "<=29999")
  

• 30000-34999（包含两端）：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=30000", Crowdfunding!$D:$D, "<=34999")
  

• 35000-39999（包含两端）：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=35000", Crowdfunding!$D:$D, "<=39999")
  

• 40000-44999（包含两端）：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=40000", Crowdfunding!$D:$D, "<=44999")
  

• 45000-49999（包含两端）：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=45000", Crowdfunding!$D:$D, "<=49999")
  

• 大于等于50000：

  =COUNTIFS(Crowdfunding!$G:$G, "passed", Crowdfunding!$D:$D, ">=50000")
  

你之前公式的错误原因 #

你写的=COUNTIFS(Crowdfunding!$G:$G,Crowdfunding!$D:$D,"<1000")参数顺序不符合语法要求，必须是「条件区域+对应条件」成对出现，正确顺序应为「状态区域、状态条件、数值区域、数值条件」。

内容的提问来源于stack exchange，提问作者Sanaah Azai