Count matching cells

[The Mage]

I have 2 columns of 30 numbers, side by side. All numbers are 1 to 6.
I need to count every time the numbers in a row both equal 2, 3, 4, or
5. (Matching pairs.)

I cannot think of how to do this without using 30 if functions for each
number, and summing the count. To clarify I need to count total
matching 2’s, then total matching 3’s, etc.

Thanks for any assistance.

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A30={2,3,4,5}),--(B1:B30={2,3,4,5}))

 

[The Mage]

Thank you so much, that was exactly what I was looking for.

 

[Bob Phillips]

my pleasure.

 

[Ashish Mathur]

Hi,

You may also use an array formula (Ctrl+Shift+Enter) to accomplish the same.

=sum(if((range=2),1,0)
=sum(if((range=3),1,0) etc.

Regards,