Count no of Lines by Date

[VbaHell]

Hello Excel World, I am looking for a way to count the number of lines received by day and name.

IE: 20/6/2011 John
20/6/2011 John
20/6/2011 Fred
22/6/2011 John
22/6/2011 Jane

= 20/6/2011 John 2
= 20/6/2011 Fred 1
= 22/6/2011 John 1
= 22/6/2011 Jane 1

Any help will be welcomed

 

[VoG]

Try a Pivot Table

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Who		Count of Who	Column Labels
2	20/06/2011	John		Row Labels	Fred	Jane	John	Grand Total
3	20/06/2011	John		20/06/2011	1		2	3
4	20/06/2011	Fred		22/06/2011		1	1	2
5	22/06/2011	John		Grand Total	1	1	3	5
6	22/06/2011	Jane
Sheet8

 

[T. Valko]

Hello Excel World, I am looking for a way to count the number of lines received by day and name.

IE: 20/6/2011 John
20/6/2011 John
20/6/2011 Fred
22/6/2011 John
22/6/2011 Jane

= 20/6/2011 John 2
= 20/6/2011 Fred 1
= 22/6/2011 John 1
= 22/6/2011 Jane 1

Any help will be welcomed

You could do something like this...

Raw data:

Book1
	A	B
2	6/20/2011	John
3	6/20/2011	John
4	6/20/2011	Fred
5	6/22/2011	John
6	6/22/2011	Jane
Sheet1

Summary Table:

Book1
	D	E	F	G
1	_	John	Fred	Jane
2	6/20/2011	2	1	0
3	6/22/2011	1	0	1
Sheet1

Formula entered in E2:

If you're using Excel 2007 or later:

=COUNTIFS($A$2:$A$6,$D2,$B$2:$B$6,E$1)

This one will work in any version of Excel:

=SUMPRODUCT(--($A$2:$A$6=$D2),--($B$2:$B$6=E$1))

Copy across to G2 then down to E3:G3.

 

[VbaHell]

This is perfect, thank you guru

 

[T. Valko]

This is perfect, thank you guru

You're welcome. Thanks for the feedback!