sumif with four criteria???

  • Thread starter Thread starter joemeshuggah
  • Start date Start date
J

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