| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting matches from more columns
Hi,
I have a list something like this: A B P-no. Week 336 2 421 2 421 3 643 3 643 3 336 4 421 4 642 5 In separate colums I need to list how many lines with each P-no. I have in each week, like this: C D E F Week 336 421 643 2 ? ? ? 3 ? ? ? 4 ? ? ? 5 ? ? ? Hope someone can help with formular at questionmarks. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting matches from more columns
Try this in D2:
=SUMPRODUCT(($B$2:$B$1000=$C2)*($A$2:$A$1000=D$1)) I have assumed you have 1000 items in your list - adjust if necessary. Copy the formula across and down as required. Hope this helps. Pete On Nov 9, 12:04 am, PedersenJ wrote: Hi, I have a list something like this: A B P-no. Week 336 2 421 2 421 3 643 3 643 3 336 4 421 4 642 5 In separate colums I need to list how many lines with each P-no. I have in each week, like this: C D E F Week 336 421 643 2 ? ? ? 3 ? ? ? 4 ? ? ? 5 ? ? ? Hope someone can help with formular at questionmarks. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting matches from more columns
Thanks Pete,
It works perfect! Best regards Jens "Pete_UK" skrev: Try this in D2: =SUMPRODUCT(($B$2:$B$1000=$C2)*($A$2:$A$1000=D$1)) I have assumed you have 1000 items in your list - adjust if necessary. Copy the formula across and down as required. Hope this helps. Pete On Nov 9, 12:04 am, PedersenJ wrote: Hi, I have a list something like this: A B P-no. Week 336 2 421 2 421 3 643 3 643 3 336 4 421 4 642 5 In separate colums I need to list how many lines with each P-no. I have in each week, like this: C D E F Week 336 421 643 2 ? ? ? 3 ? ? ? 4 ? ? ? 5 ? ? ? Hope someone can help with formular at questionmarks. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting number of times data matches | Excel Worksheet Functions | |||
| counting line by line matches of 2 columns possible in excel ? | Excel Discussion (Misc queries) | |||
| How to identify same row matches in two columns of #s using Excel | Excel Discussion (Misc queries) | |||
| I need to compare to columns and indicate the matches in another | New Users to Excel | |||
| counting matches | Excel Discussion (Misc queries) | |||
Linear Mode
