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!

=SUMPRODUCT(-(B2:B5=DATE(2005,1,1)),-(C2:C5="eating"))


"AzMan" wrote:

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(2 005,3,11))*(C2:C5="eating"))


"Teethless mama" wrote:

=SUMPRODUCT(-(B2:B5=DATE(2005,1,1)),-(C2:C5="eating"))


"AzMan" wrote:

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!

=SUMPRODUCT(--(B2:B638352),--(B2:B6<38423),--(C2:C6="eating"))

--
Gary''s Student - gsnu200717


"AzMan" wrote:

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!

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.

"Teethless mama" wrote:

=SUMPRODUCT(-(B2:B5=DATE(2005,1,1)),-(C2:C5="eating"))


"AzMan" wrote:

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!