formula for calculating repeat occurences between specified dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet which is as follows:

NAME DATE OFFENCE
J.Smith 1-Jan-05 eating
M.Patel 9-feb-05 drinking
T.Pot 11-mar-05 eating
E.Colin 13-dec-06 eating
A.Idle 6-apr-06 eating

I want to calculate the amount of times 'eating' occurs between 1-jan-05 and
11-mar-05. I've tried a formula which counts the occurences between a range,
but because there are 'sort' buttons over name, date and offence and the data
shifts location each time a user 'sorts' something, my figures are no longer
accurate. Please help!
 
ignore the previous reply.

=SUMPRODUCT((B2:B5>=DATE(2005,1,1))*(B2:B5<=DATE(2005,3,11))*(C2:C5="eating"))
 
Hi, the formula you have below gives back a result of '1' for the word
'eating'. Surely it should be '2' if the date range was between b2 and b5?
Thanks for responding so quickly, and hope you can help.
 
Back
Top