Excel多条件计数：按列A值统计对应列B指定前缀IP数量

核心需求实现公式 #

要统计指定区域（如A列值为1）的符合条件（以178.或14.开头）的IP数量，可使用以下公式：

=SUMPRODUCT((A2:A11=1)*((LEN(B2:B11)-LEN(SUBSTITUTE(B2:B11,"178.","")))/LEN("178.") + (LEN(B2:B11)-LEN(SUBSTITUTE(B2:B11,"14.","")))/LEN("14.")))

公式拆解 #

• (A2:A11=1)：筛选A列值为目标区域的行，符合条件返回1，否则0，实现区域过滤
• (LEN(B2:B11)-LEN(SUBSTITUTE(B2:B11,"178.","")))/LEN("178.")：计算单个单元格中178.开头的IP数量，通过替换前后的长度差除以178.的字符长度（4），得到该前缀的出现次数
• 后半部分(LEN(B2:B11)-LEN(SUBSTITUTE(B2:B11,"14.","")))/LEN("14.")：同理计算14.开头的IP数量
• SUMPRODUCT：将区域过滤条件与对应行的IP数量相乘后求和，仅统计符合区域条件的行的IP总数

批量统计所有区域 #

若需一次性统计1、2、3、4等所有区域的IP数量，可按以下步骤操作：

1. 在空白列（如D列）依次输入区域编号：1、2、3、4
2. 在对应单元格（如E2）输入公式，将固定区域值替换为单元格引用：

=SUMPRODUCT((A$2:A$11=D2)*((LEN(B$2:B$11)-LEN(SUBSTITUTE(B$2:B$11,"178.","")))/LEN("178.") + (LEN(B$2:B$11)-LEN(SUBSTITUTE(B$2:B$11,"14.","")))/LEN("14.")))

3. 下拉E2单元格至E5，即可自动计算每个区域的在用IP总数

样本数据验证 #

结合提供的样本数据，公式计算结果如下：

• 区域1：3个IP
• 区域2：18个IP
• 区域3：16个IP
• 区域4：17个IP

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