is there a function that will allow you to sumif based on four criteria
across two separate worksheets?

i have a sales rep id with a start and end date on one worksheet and order
information (which includes sales rep id, order date, and order type).

i want to be able to sum the orders for each sales rep provided that
1. the sales ids match AND
2. the order date is greater than the rep's start date AND
3. the order date is less than the rep's end date
4. for a specific order type

rep id is in column a on both sheets,
start date is in column b of workbook1,
end date is in column c of workbook1,
order date is in column b of workbook2,
order type is in column c of workbook2

How will you know what order type you are looking up?

Tom

sorry! i will be adding the order types to column d in workbook1 as a
reference

"TomPl" wrote:

How will you know what order type you are looking up?

Tom

sorry! i will be adding the order type to column d on worksheet1 as a
reference

"TomPl" wrote:

How will you know what order type you are looking up?

Tom

Yes! It is SUMPRODUCT

=SUMPRODUCT(--(A1:A100="apple"), --(B1:B100<"computer"), C1:C100)
This will sum al the C cells where the corresponding A cell = "apple" and B
cell is not "computer"
or more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"joemeshuggah" wrote in message
...
is there a function that will allow you to sumif based on four criteria
across two separate worksheets?

i have a sales rep id with a start and end date on one worksheet and order
information (which includes sales rep id, order date, and order type).

i want to be able to sum the orders for each sales rep provided that
1. the sales ids match AND
2. the order date is greater than the rep's start date AND
3. the order date is less than the rep's end date
4. for a specific order type

rep id is in column a on both sheets,
start date is in column b of workbook1,
end date is in column c of workbook1,
order date is in column b of workbook2,
order type is in column c of workbook2

well, you'll want

=SUMPRODUCT(--(sheet2!A2:A10000=A2),--(sheet2!B2:B10000<=B2),--(sheet2!C2:C10000=B2))

to count instances where rep id matches and order date is within work dates

But not sure how order type plays into it...

"joemeshuggah" wrote:

is there a function that will allow you to sumif based on four criteria
across two separate worksheets?

i have a sales rep id with a start and end date on one worksheet and order
information (which includes sales rep id, order date, and order type).

i want to be able to sum the orders for each sales rep provided that
1. the sales ids match AND
2. the order date is greater than the rep's start date AND
3. the order date is less than the rep's end date
4. for a specific order type

rep id is in column a on both sheets,
start date is in column b of workbook1,
end date is in column c of workbook1,
order date is in column b of workbook2,
order type is in column c of workbook2

What are you summing?
Where is amount?

Tom

So, assuming that your worksheets really are named A and B, then assuming the
data you want to sum is in column D of worksheet B, put this formula in a!E2.

=SUMPRODUCT(--(b!A2:A65536=a!A2),--(b!B2:B65536=a!B2),--(b!B2:B65536<=a!C2),b!D2:D65536)

You might consider reducing the size of the ranges, but each range must be
exactly the same size and you cannot use the column identifier e.g.( D:D )
when working with excel 2003.

Tom

This takes into consideration the order type and assumes data to be summed in
is column D of sheet b.

=SUMPRODUCT(--(b!A2:A65536=a!A2),--(b!B2:B65536=a!B2),--(b!B2:B65536<=a!C2),--(b!C2:C65536=a!D2),b!D2:D65536)

Tom