Sum If multiple conditions

[EE]

Hi all

I have a database that has a date range, a territory look up range and
a sales value range by Category..

Src Date Territory Sales Value Category
09/01/2007 AAA 1.00 Product
09/01/2007 BBB 1.00 Product
09/05/2007 CCC 1.00 Product
09/05/2007 AAA 1.00 Service
09/07/2007 BBB 1.00 Service
09/07/2007 CCC 1.00 Service
09/11/2007 AAA 1.00 Product
09/11/2007 BBB 1.00 Service

I have a second list that has agents that have from 1 to 13
territories as their payment criteria.
Agent Territory 1 Territory 2
Jones AAA CCC

I need to be able to create a formula that will look up with in a
defined date range (say 09/03/2007 to 09/10/2007), the value (Sum) of
the orders that have assigned to these agents based on all of their
assigned territories as shown in the second tabel above.

Many thanks for any help. Much appreciated.

Best
Prasad

 

[Guest]

With Date1 in A1 and Date2 in B1, all dates listed in A4:A11, Sales listed in
C4:C11, try this:
=SUMPRODUCT(--(A4:A11>=A1),--(A4:A11<=B1)*(C4:C11))

Regards,
Ryan--

 

[EE]

Thanks for the reply Ryan. But this solves only one part of my
problem.

My final objective is to get the final value for each Sales person for
their defined territories within these dates.

Best
Prasad

 

[Guest]

Ok, let's try this. I can't tell where your own data is, but I'll tell you
where your data is for me, and hopefully you will be able to extract a
solution from what I post here.

Scr listed in A2:A9
Date listed in B2:B9
Territory listed in C2:C9
H1 = 9/1/2007
H2 = 9/5/2007
H3 = AAA
H4 = CCC

Finally, E3 =
=SUMPRODUCT(--(A2:A9>=H1),--(A2:A9<=H2),--(B2:B9=H3)--(B2:B9=H4)*(C2:C9))
The result is 3.

I'm not exactly sure how you want to handle the name, but follow my format,
and you should be able to plug it into the function above, however it is
suitable for you to do so.

Is this something that you can work with? As I understand it, a user can
list up to 30 criteria in his/her SUMPRODUCT function.

Regards,
Ryan--