I am trying to determine how many items in a pair of columns meet
certain criteria. For instance, I have two rows, side by side, where
each cell contains a single digit, a 1, 2 or 3. I need to count the
number of rows where both columns contain a 1, or both contain a 2, or
where one column contains a 1 and the row next to it contains a 3. In
the example list below I would need to determine how many rows contain
1’s in both columns, and how many rows contain a 3 in one column and a
2 in the other, etc. So, given the following data...

Code:
--------------------

Row A Row B
1 1
1 2
1 1
2 3
3 1
3 3

--------------------

...the results I am looking for would resemble something like this:
1-1 = 2
1-2 = 1
1-3 = 0
2-1 = 0
2-2 = 0
3-3 = 1
3-1 = 1
3-2 = 0
3-3 = 1


--
JimDandy
------------------------------------------------------------------------
JimDandy's Profile: http://www.excelforum.com/member.php...o&userid=16578
View this thread: http://www.excelforum.com/showthread...hreadid=527797

Hi,

Let's assume that your data are in say A2:A7, and B2:B7.
In two new columns, say C2:C10 and D2:D10, enter the combinations, as shown
below:

C D
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3

In E2, enter the following formula and autofill it down to E10.
=SUMPRODUCT(($A$2:$A$7=C2)*($B$2:$B$7=D2))


Regards,
B. R. Ramachandran



"JimDandy" wrote:


I am trying to determine how many items in a pair of columns meet
certain criteria. For instance, I have two rows, side by side, where
each cell contains a single digit, a 1, 2 or 3. I need to count the
number of rows where both columns contain a 1, or both contain a 2, or
where one column contains a 1 and the row next to it contains a 3. In
the example list below I would need to determine how many rows contain
1s in both columns, and how many rows contain a 3 in one column and a
2 in the other, etc. So, given the following data...

Code:
--------------------

Row A Row B
1 1
1 2
1 1
2 3
3 1
3 3

--------------------

...the results I am looking for would resemble something like this:
1-1 = 2
1-2 = 1
1-3 = 0
2-1 = 0
2-2 = 0
3-3 = 1
3-1 = 1
3-2 = 0
3-3 = 1


--
JimDandy
------------------------------------------------------------------------
JimDandy's Profile: http://www.excelforum.com/member.php...o&userid=16578
View this thread: http://www.excelforum.com/showthread...hreadid=527797

That was just the ticket, thanks for teh quick and accurate reply


--
JimDandy
------------------------------------------------------------------------
JimDandy's Profile: http://www.excelforum.com/member.php...o&userid=16578
View this thread: http://www.excelforum.com/showthread...hreadid=527797