| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
=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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching cells | Excel Discussion (Misc queries) | |||
| Character Count Range of Cells | Excel Discussion (Misc queries) | |||
| Character Count Range of Cells | Excel Discussion (Misc queries) | |||
| how 2 Count number of cells that have specific condition format? | Excel Worksheet Functions | |||
| count cells that have *text1* and don't have *text2* | Excel Discussion (Misc queries) | |||
Linear Mode
