Excel conditional counting

Question

This is a doubt that arose in my workplace, and it should be pretty straightforward.

We have two columns of numbers, say:

And we want to get the number of rows that have equal numbers (2), without a helping column for each comparison. We can't get a working matrix operation (wich is the way we think correct). Any ideas?

Thanks in advance!

I like Excel very much · Accepted Answer · 2020-10-07 23:05:11Z

1

Three more to pile on here:

Condensed array function:

{=SUM(--(A1:A10=B1:B10))}

Same function, but wrapped in a SUMPRODUCT rather than an array function:

=SUMPRODUCT(--(A1:A10=B1:B10))

Ignores blanks:

=SUMPRODUCT(--(A1:A10=B1:B10)*NOT(A1:A10="")*NOT(B1:B10=""))

answered Oct 7, 2020 at 23:05

I like Excel very much

7486 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AticusFinch Over a year ago

Great! I moved the accepted answer from mine to yours because I kind-of despise array functions :) Thanks for this info!

double-beep · Accepted Answer · 2019-06-09 19:24:06Z

1

We got it using:

{=SUM(IF(A1:A10=B1:B10;1;0))}

edited Jun 9, 2019 at 19:24

double-beep

5,67019 gold badges43 silver badges50 bronze badges

answered Mar 3, 2009 at 17:59

AticusFinch

2,4413 gold badges26 silver badges32 bronze badges

Collectives™ on Stack Overflow

Excel conditional counting

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related