How to retrieve a value based on 2 different criteria
 

My table looks like this:

Type Date Officer1 Officer2
Deadline 01/01/2007 10 0
Brod 05/01/2007 7 10
Deadline 12/01/2207 15 5
Deadline 15/01/2007 10 10
Deadline 17/01/2007 10 5
Fly Spray 17/01/2007 2 0
Fly Spray 28/01/2007 0 1
Neosorexa 03/02/2007 0 10
Deadline 15/02/2007 10 0
Bait Trays 27/02/2007 20 0
Fly Spray 07/03/2007 3 1
Deadline 16/03/2007 5 5


How do I total up the number of each type, for the officers, for each
seperate month?

I.e. 'Deadline' for January is 65 in total (45 for Officer1 and 20 for
Officer2)

In reality my table contain over 150 entries and the dates don't always run
in sequence, so I need it to be able to pick out the month (I imagine using
a 01/01/2007<31/01/2007 somewhere) and pick out the type, then total it up.

Hope you can help....Thanx

How to retrieve a value based on 2 different criteria
 

=SUMPRODUCT((A2:A13="Deadline")*(MONTH(B2:B13)=1)* C2:D13)

"littlejess22" wrote:

My table looks like this:

Type Date Officer1 Officer2
Deadline 01/01/2007 10 0
Brod 05/01/2007 7 10
Deadline 12/01/2207 15 5
Deadline 15/01/2007 10 10
Deadline 17/01/2007 10 5
Fly Spray 17/01/2007 2 0
Fly Spray 28/01/2007 0 1
Neosorexa 03/02/2007 0 10
Deadline 15/02/2007 10 0
Bait Trays 27/02/2007 20 0
Fly Spray 07/03/2007 3 1
Deadline 16/03/2007 5 5


How do I total up the number of each type, for the officers, for each
seperate month?

I.e. 'Deadline' for January is 65 in total (45 for Officer1 and 20 for
Officer2)

In reality my table contain over 150 entries and the dates don't always run
in sequence, so I need it to be able to pick out the month (I imagine using
a 01/01/2007<31/01/2007 somewhere) and pick out the type, then total it up.

Hope you can help....Thanx

How to retrieve a value based on 2 different criteria
 

Its not working!!! I'm sure I've followed it properly, but its returning the
#VALUE code and I can't seem to sort it. I've done the MONTH formula by
itself and that seems to be returning FALSE, is this the problem? Hope you
can still help me!!!

"Toppers" wrote:

=SUMPRODUCT((A2:A13="Deadline")*(MONTH(B2:B13)=1)* C2:D13)

"littlejess22" wrote:

My table looks like this:

Type Date Officer1 Officer2
Deadline 01/01/2007 10 0
Brod 05/01/2007 7 10
Deadline 12/01/2207 15 5
Deadline 15/01/2007 10 10
Deadline 17/01/2007 10 5
Fly Spray 17/01/2007 2 0
Fly Spray 28/01/2007 0 1
Neosorexa 03/02/2007 0 10
Deadline 15/02/2007 10 0
Bait Trays 27/02/2007 20 0
Fly Spray 07/03/2007 3 1
Deadline 16/03/2007 5 5


How do I total up the number of each type, for the officers, for each
seperate month?

I.e. 'Deadline' for January is 65 in total (45 for Officer1 and 20 for
Officer2)

In reality my table contain over 150 entries and the dates don't always run
in sequence, so I need it to be able to pick out the month (I imagine using
a 01/01/2007<31/01/2007 somewhere) and pick out the type, then total it up.

Hope you can help....Thanx