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

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

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