How to count sets of numbers
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 |
How to count sets of numbers
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 |
How to count sets of numbers
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 |
All times are GMT +1. The time now is 11:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com