Excel - Count how many rows have equal numbers in two columns

4
votes

enter image description here

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.

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...

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

1
excel

1 Answers

6
votes

Try this one:

=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))

enter image description here