DCOUNT

[Ride The Lightning]

I have a range of data containing a list of football matches, the relevant columns being:
A - Date
B - Home Team

In cell M2, I want a formula to add the number of times a team's name appears in column B depending on the date. For example, I want to count the number of times the name "Chelsea" appears in column B after the 20/08/2001. To achieve this, in K2 I type the appropriate team name, in this case "Chelsea", and in L2 the date ">20/08/2001". I tried the following formula to no avail : =DCOUNT(A1:G71,2,K1:L2)
(the answer I got was '0', the answer I should get from the sample data is '1')

What do I need to do to get my formula working?

Sample data if required:
Row2 - 18/08/01, Charlton
Row3 - 18/08/01, Derby
Row4 - 18/08/01, Leeds
Row5 - 18/08/01, Leicester
Row6 - 18/08/01, Liverpool
Row7 - 18/08/01, Middlesboro
Row8 - 18/08/01, Sunderland
Row9 - 18/08/01, Tottenham
Row10 - 19/08/01, Chelsea
Row11 - 19/08/01, Man Utd
Row12 - 20/08/01, Everton
Row13 - 21/08/01, Arsenal
Row14 - 21/08/01, Bolton
Row15 - 21/08/01, Ipswich
Row16 - 22/08/01, Blackburn
Row17 - 22/08/01, Fulham
Row18 - 25/08/01, Arsenal
Row19 - 25/08/01, Blackburn
Row20 - 25/08/01, Everton
Row21 - 25/08/01, Fulham
Row22 - 25/08/01, Ipswich
Row23 - 25/08/01, Southampton
Row24 - 25/08/01, West Ham
Row25 - 26/08/01, Aston Villa
Row26 - 26/08/01, Newcastle
Row27 - 27/08/01, Bolton
Row28 - 08/09/01, Chelsea
Row29 - 08/09/01, Derby
Row30 - 08/09/01, Leeds
Row31 - 08/09/01, Leicester
Row32 - 08/09/01, Liverpool
Row33 - 08/09/01, Man Utd
Row34 - 08/09/01, Middlesboro
Row35 - 08/09/01, Sunderland
Row36 - 09/09/01, Charlton
Row37 - 09/09/01, Tottenham

 

[Peter_SSs]

Ride The Lightning

I think this will do what you want. I have moved everything over close to column A to make display on this board easier but you should be able to modify this to suit your needs.

The formula in E1 is:
=SUMPRODUCT(--(A1:A10>D1),--(B1:B10=C1))

Mr Excel.xls
	A	B	C	D	E
1	18/08/2001	Chelsea	Chelsea	18/08/2001	3
2	18/08/2001	Tottenham
3	19/08/2001	Chelsea
4	19/08/2001	Man Utd
5	20/08/2001	Everton
6	21/08/2001	Arsenal
7	21/08/2001	Chelsea
8	21/08/2001	Ipswich
9	22/08/2001	Blackburn
10	22/08/2001	Chelsea
Count

 

[Ride The Lightning]

Thank you, works perfect now