A B C
1 name date offence
2 j.smith 01-Jan-05 eating
3 r.donlad 09-Feb-05 drinking
4 a.pedrosa 11-Mar-05 eating
5 m.patel 13-Dec-06 eating
6. t.pot 06-Apr-06 eating

above is a spreadsheet i am working on. above each column is a 'sort'
button which sorts by name, date or offence. I need to calculate how many
times an offence is committed between specific term dates. I don't think I
can use a 'range' of cells, because if one of the 'sort' buttons is clicked
on the, data changes within that range.

if i want to calculate the amount of times 'eating' occurs between 01-jan-05
and 13-dec-06, the result returned should be '3', regardless if any 'sort'
buttons are pressed. Please help!!

D1=01/01/05
D2=13/12/06
D3="eating"

If the dates are "inclusive" result is 4

=SUMPRODUCT(--($B$2:$B$6=D1),--($B$2:$B$6<=D2),--($C$2:$C$6=D3))

"AzMan" wrote:

A B C
1 name date offence
2 j.smith 01-Jan-05 eating
3 r.donlad 09-Feb-05 drinking
4 a.pedrosa 11-Mar-05 eating
5 m.patel 13-Dec-06 eating
6. t.pot 06-Apr-06 eating

above is a spreadsheet i am working on. above each column is a 'sort'
button which sorts by name, date or offence. I need to calculate how many
times an offence is committed between specific term dates. I don't think I
can use a 'range' of cells, because if one of the 'sort' buttons is clicked
on the, data changes within that range.

if i want to calculate the amount of times 'eating' occurs between 01-jan-05
and 13-dec-06, the result returned should be '3', regardless if any 'sort'
buttons are pressed. Please help!!