SUMIF with dual criteria?

I want to use a SUMIF statement that will only sum items that meet 2 criteria that are located within 2 target areas.

Example:
column A has date
column B has part numbers
column C has cost

So I want to get a total cost of all of a particilar part number used within a certain date range.

The SUMIF statement worked perfect for only finding date and cost, but now I want to add a 3rd component into the mix.

Any experts out there that can help?
Thanks

Originally Posted by surfengine

I want to use a SUMIF statement that will only sum items that meet 2 criteria that are located within 2 target areas.

Example:
column A has date
column B has part numbers
column C has cost

So I want to get a total cost of all of a particilar part number used within a certain date range.

The SUMIF statement worked perfect for only finding date and cost, but now I want to add a 3rd component into the mix.

Any experts out there that can help?
Thanks

As Kieran said, SUMPRODUCT is probably the way to go.

Try something like the following: (I just assumed 15 rows, easily modified)

=SUMPRODUCT(($A$1:$A$15>=DATEVALUE("10/10/2006"))*($A$1:$A$15<=DATEVALUE("11/03/2006")),--($B$1:$B$15="A101"),$C$1:$C$15)

The problem with this is that the dates and part number being looked for are in the formula itself. The following formula is better: (The assumption being that the start date is in E1, the end date in F1, and the part number in G1)

=SUMPRODUCT(($A$1:$A$15>=E1)*($A$1:$A$15<=F1),--($B$1:$B$15=G1),$C$1:$C$15)

Hopefully this makes sense. If you don't want a date range, but instead want to only have a total for the part since a particular date, you'd modify this as follows:

=SUMPRODUCT(--($A$1:$A$15>=E1),--($B$1:$B$15=G1),$C$1:$C$15)

Note that the --(...) is a convention to make the sumproduct formula treat the results of these expressions as numbers instead of True/False values. You don't really need to worry about that part too much unless you're curious.

Hope that helps,
Scott

Thanks to the great help from both of you.
After a little messing around I got it to work.
The problem I have now, is the part number is located in more than 1 column.
Using your reference formula below seems to only allow 1 column of data for array 2.

Originally Posted by Maistrye

=SUMPRODUCT(($A$1:$A$15>=E1)*($A$1:$A$15<=F1),--($B$1:$B$15=G1),$C$1:$C$15)

Instead of using (B1:B15), I would want to use (B1:F15)

I am thinking I can just repeat this formula 1 time for each then add the totals, but I am sure there is another better way to achieve this.

Thanks.

Originally Posted by surfengine

Thanks to the great help from both of you.
After a little messing around I got it to work.
The problem I have now, is the part number is located in more than 1 column.
Using your reference formula below seems to only allow 1 column of data for array 2.



Instead of using (B1:B15), I would want to use (B1:F15)

I am thinking I can just repeat this formula 1 time for each then add the totals, but I am sure there is another better way to achieve this.

Thanks.

It depends on what you mean by multiple columns.

Do you mean in B1 you might have "AF", in C1 "12", in D1 "00", in E1 "ST", in F1 "RX", with the actual part number being AF1200STRX? This might be quite difficult to do, depending on the data you use. It may be necessary to create a helper column.

Or do you mean that you would have 5 parts in that row, AF, 12, 00, ST, RX? This situation is easier, but it depends on how you want the cost treated (ie. is the cost the total cost for all 5 parts, or is it the cost for each individual part?).

Or I might be putting in more thought than is necessary. :-P

Scott

Originally Posted by Maistrye

Or do you mean that you would have 5 parts in that row, AF, 12, 00, ST, RX? This situation is easier, but it depends on how you want the cost treated (ie. is the cost the total cost for all 5 parts, or is it the cost for each individual part?).

Or I might be putting in more thought than is necessary. :-P

Scott

This is getting complicated I think
Each row contains up to 5 parts numbers. Each part number is 10 digits long.
Your above comment made me realize that I have only the total part cost for each row and not individual part cost.

I think this is way to complex for anything I could hope to recieve an answer for in a forum...but I will post it anyways:

I need to determine the cost and usage for a list of parts by month.
The format the parts are in is up to 5 in a row.
I would need to create a parts table and a cost table.
Then sort by month and seperate by part type.

BTW - I did use the SUMPRODUCT for calc the cost by month and product type. thx

Originally Posted by surfengine

This is getting complicated I think
Each row contains up to 5 parts numbers. Each part number is 10 digits long.
Your above comment made me realize that I have only the total part cost for each row and not individual part cost.

I think this is way to complex for anything I could hope to recieve an answer for in a forum...but I will post it anyways:

I need to determine the cost and usage for a list of parts by month.
The format the parts are in is up to 5 in a row.
I would need to create a parts table and a cost table.
Then sort by month and seperate by part type.

BTW - I did use the SUMPRODUCT for calc the cost by month and product type. thx

Is the price for each part the same for the whole month?
If the price changes, you'll not get an exact answer.

How's your linear algebra?

If it's good, you can determine the cost for all the parts for the whole month. And you can determine the count of each part for that month.

Then, you just have to set up the system of equations to solve for price of each part. For some, it will be as simple as putting a price in because you have only one item on a given line. You might even be able to do it with Excel. :-P I'm not sure, I'll have to look.

I can't guarantee I'll be able to give you a response before Tuesday, since I'm off Monday. We'll see, I sometimes don't bother turning on my comp at home.

Scott