| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the same numerical occurence in 2 separate columns
Hello, Any help would be greatly appreciated. I'm trying to count the number of occurences of a number in on column and then a y or n in the second column relating to that number. IE Column A has as series of numbers that run from 1 to 4. Column B has either a Y or N. Example: Column A has 1 in cell one and Column B has Y in cell one and I'm trying to count the number of times this happens in the column. I have tried a bunch of different things, countif, sumif, Dcount A, sumproduct, etc. and none of them seem to work. Any ideas? Thank you for your help! Have a great day. -- jr100 ------------------------------------------------------------------------ jr100's Profile: http://www.excelforum.com/member.php...o&userid=31335 View this thread: http://www.excelforum.com/showthread...hreadid=564879 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the same numerical occurence in 2 separate columns
Try this:
=Sumproduct((A1:A1000=1)*(B1:B100="Y") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jr100" wrote in message ... Hello, Any help would be greatly appreciated. I'm trying to count the number of occurences of a number in on column and then a y or n in the second column relating to that number. IE Column A has as series of numbers that run from 1 to 4. Column B has either a Y or N. Example: Column A has 1 in cell one and Column B has Y in cell one and I'm trying to count the number of times this happens in the column. I have tried a bunch of different things, countif, sumif, Dcount A, sumproduct, etc. and none of them seem to work. Any ideas? Thank you for your help! Have a great day. -- jr100 ------------------------------------------------------------------------ jr100's Profile: http://www.excelforum.com/member.php...o&userid=31335 View this thread: http://www.excelforum.com/showthread...hreadid=564879 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the same numerical occurence in 2 separate columns
=SUMPRODUCT(--($A$1:$A$5000=*1)*(--(*$B$1:$B$5000="*Y*"))) Change the 1 and Y to what ever combo More help on sumproduct can be find on attached link http://www.xldynamic.com/source/xld.SUMPRODUCT.html VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=564879 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the same numerical occurence in 2 separate columns
A pivot table would be an easy way to do this. Your data needs headings for
this. Click a cell in your data, then select PivotTable and PivotChart Report from the Data menu. Your data is a Microsoft Office Excel list, and you want to create a PivotTable. Click Next. Make sure Excel has selected the correct range, then click Next. Select where you want to put the pitvot table, then click Finish. Add the column A field (the numbers) to the Row Area. Then, add the column B field (Y or N) to the Row Area. Finally, add the column B field to the Data Area. By default, the pivot table will count the data items. Hope this helps, Hutch "jr100" wrote: Hello, Any help would be greatly appreciated. I'm trying to count the number of occurences of a number in on column and then a y or n in the second column relating to that number. IE Column A has as series of numbers that run from 1 to 4. Column B has either a Y or N. Example: Column A has 1 in cell one and Column B has Y in cell one and I'm trying to count the number of times this happens in the column. I have tried a bunch of different things, countif, sumif, Dcount A, sumproduct, etc. and none of them seem to work. Any ideas? Thank you for your help! Have a great day. -- jr100 ------------------------------------------------------------------------ jr100's Profile: http://www.excelforum.com/member.php...o&userid=31335 View this thread: http://www.excelforum.com/showthread...hreadid=564879 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the same numerical occurence in 2 separate columns
Thank you for the help. Both forumula's work awesome. Have a great day! -- jr100 ------------------------------------------------------------------------ jr100's Profile: http://www.excelforum.com/member.php...o&userid=31335 View this thread: http://www.excelforum.com/showthread...hreadid=564879 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the same numerical occurence in 2 separate columns
WE appreciate the feed-back.
-- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "jr100" wrote in message ... Thank you for the help. Both forumula's work awesome. Have a great day! -- jr100 ------------------------------------------------------------------------ jr100's Profile: http://www.excelforum.com/member.php...o&userid=31335 View this thread: http://www.excelforum.com/showthread...hreadid=564879 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count if two columns match different criteria | Excel Worksheet Functions | |||
| Separate cell text FirstnameLastname into two columns | Excel Discussion (Misc queries) | |||
| Count number of times two columns have desired values | Excel Discussion (Misc queries) | |||
| Breaking up a string into separate columns | Excel Worksheet Functions | |||
| Count certain changes between columns | Excel Worksheet Functions | |||
Linear Mode
