Excel-计算两列中相等编号的行数 [英] Excel - Count how many rows have equal numbers in two columns

问题描述

如我的图片中所述，目标是计算A列中的"AAA"，"BBB"和"CCC"在其相应行中的B和C彼此相等的次数. B和C列中的值是整数.我没有隐藏任何小数位.

As described in my picture, the goal is to count how many times "AAA", "BBB", and "CCC" in column A have column B and C equal to each other in their respective rows. The values in column B and C are whole numbers. I haven't hidden any decimal places.

我意识到我可以简单地在B1 = C1(B2 = C2 ...等)的位置添加一个额外的列，然后运行COUNTIFS公式来计算TRUE单元的数量.那是计划B.我觉得有一种方法可以解决此问题，但又不会使我的工作表充满额外的数据列...

I realize I could simply add an extra column where I go B1=C1 (B2=C2... and so on), and then running a COUNTIFS formula to count the number of TRUE cells. That's plan B. I feel like there's a way to do this without bloating my worksheet with an extra column of data though...

我引用了以下线程:
计数一个数字的次数在excel中的两列之间找到

I referenced the following thread:
Count number of times a number is found between two columns in excel

推荐答案

尝试以下方法:

=SUMPRODUCT((A:A="AAA")*(B:B=C:C))

我也建议您使用精确范围来加快公式的速度:

Also I suggets you to use exact ranges to make formula faster:

=SUMPRODUCT((A1:A100="AAA")*(B1:B100=C1:C100))

这篇关于Excel-计算两列中相等编号的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！