sumif with four criteria???

[joemeshuggah]

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

 

[TomPl]

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

Tom

 

[joemeshuggah]

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

 

[joemeshuggah]

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

 

[Bernard Liengme]

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

 

[Sean Timmons]

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...

 

[TomPl]

What are you summing?
Where is amount?

Tom

 

[TomPl]

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!D265536)

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 )
when working with excel 2003.

Tom

 

[TomPl]

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!D265536)

Tom