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 D210, 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
> 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
>
>

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