Count matching cells
 

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.


--
The Mage
------------------------------------------------------------------------
The Mage's Profile: http://www.excelforum.com/member.php...o&userid=27341
View this thread: http://www.excelforum.com/showthread...hreadid=468496

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

--
HTH

Bob Phillips

"The Mage" wrote in
message ...

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.


--
The Mage
------------------------------------------------------------------------
The Mage's Profile:
http://www.excelforum.com/member.php...o&userid=27341
View this thread: http://www.excelforum.com/showthread...hreadid=468496

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


--
The Mage
------------------------------------------------------------------------
The Mage's Profile: http://www.excelforum.com/member.php...o&userid=27341
View this thread: http://www.excelforum.com/showthread...hreadid=468496

my pleasure.

--
HTH

Bob Phillips

"The Mage" wrote in
message ...

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


--
The Mage
------------------------------------------------------------------------
The Mage's Profile:
http://www.excelforum.com/member.php...o&userid=27341
View this thread: http://www.excelforum.com/showthread...hreadid=468496

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,

"The Mage" wrote:


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 2s, then total matching 3s, etc.

Thanks for any assistance.


--
The Mage
------------------------------------------------------------------------
The Mage's Profile: http://www.excelforum.com/member.php...o&userid=27341
View this thread: http://www.excelforum.com/showthread...hreadid=468496