Counting the number of occurrences in a data range

[harry333]

I have two columns in a spreadsheet. Column A has 'dates' and Column B has 'Newspaper Titles'. e.g


12/01/2010 Daily Express
12/01/2010 Daily Mail
12/01/2010 Daily Mail
12/01/2010 Daily Telegraph
12/01/2010 Independent
12/01/2010 Independent
12/01/2010 Metro
12/01/2010 Sun
12/01/2010 Times

I want to count the number of times a newspaper occurs in Column B on the same date in Column A. In the example above the Daily Mail would get a score of two next to each entry as it appears twice of the 12/01/2010.

what formula do i need to use?

Thanks

 

[VoG]

Try

=SUMPRODUCT(--(A1:A9="12/01/2010"+0),--(B1:B9="Daily Mail"))

 

[T. Valko]

I have two columns in a spreadsheet. Column A has 'dates' and Column B has 'Newspaper Titles'. e.g


12/01/2010 Daily Express
12/01/2010 Daily Mail
12/01/2010 Daily Mail
12/01/2010 Daily Telegraph
12/01/2010 Independent
12/01/2010 Independent
12/01/2010 Metro
12/01/2010 Sun
12/01/2010 Times

I want to count the number of times a newspaper occurs in Column B on the same date in Column A. In the example above the Daily Mail would get a score of two next to each entry as it appears twice of the 12/01/2010.

what formula do i need to use?

Thanks

What version of Excel are you using?

 

[harry333]

Excel 2007 on Windows XP

 

[T. Valko]

Excel 2007 on Windows XP

With your data in the range A2:B10...

Use cells to hold the criteria:

• D2 = some date like 12/1/2010
• E2 = some title like Daily Mail

Then:

=COUNTIFS(A2:A10,D2,B2:B10,E2)