SUMIF on 2 criteria...

[Stu Dapples]

I have a sheet which contains product family and the machine on which they are made, I need to sum the demand for a given week (contained in another column) if Column B is "Lorry" and Column I is "T1"....

I need to do this for 3 product types (ferrari, car and lorry) and 6 machines....

Any ideas?

 

[lenze]

Pivot Table??

lenze

 

[T. Valko]

I have a sheet which contains product family and the machine on which they are made, I need to sum the demand for a given week (contained in another column) if Column B is "Lorry" and Column I is "T1"....

I need to do this for 3 product types (ferrari, car and lorry) and 6 machines....

Any ideas?

It's not real clear (to me) what you want to do.

Try rephrasing your question something like this...

Sum D2:D10 if:
A2:A10 meets this condition
B2:B10 meets this condition

What version of Excel are you using?

 

[Stu Dapples]

It's not real clear (to me) what you want to do.

Try rephrasing your question something like this...

Sum D2:D10 if:
A2:A10 meets this condition
B2:B10 meets this condition

What version of Excel are you using?

Excel 2010

Sum I2:I40 ONLY if:

B2:B40 = Lorry
AND L2:L40 = T1 (Column L is the Week 14 demand)

I need to repeat this for B range equalling Car and Ferrari
and also I range equalling T1, T2, T3, DS or MPI

I then need to repeat the whole thing for Column M through S
so by my calcs thats potentially 15 sums it needs to do... Pivot is not giving me what I need, it is graphing each family (car lorry etc) with a week above it and I need the sum total....

Effectively, what is the sum of demand in each column which fits lorry/ T1, lorry/ T2, Lorry/ T3 etc

Hope that clarifies a little?

 

[lenze]

=SUMPRODUCT(--(B2:B40="Lorry"),--(I2:I40=""T1"),L2:L40)

lenze

Hint:You can use a cell reference for your criteria to reduce the number of formulas needed

=SUMPRODUCT(--(B2:B40=AB1),--(I2:I40=AB2),L2:L40)

 

[T. Valko]

Excel 2010

Sum I2:I40 ONLY if:

B2:B40 = Lorry
AND L2:L40 = T1 (Column L is the Week 14 demand)

I need to repeat this for B range equalling Car and Ferrari
and also I range equalling T1, T2, T3, DS or MPI

I then need to repeat the whole thing for Column M through S
so by my calcs thats potentially 15 sums it needs to do... Pivot is not giving me what I need, it is graphing each family (car lorry etc) with a week above it and I need the sum total....

Effectively, what is the sum of demand in each column which fits lorry/ T1, lorry/ T2, Lorry/ T3 etc

Hope that clarifies a little?

Let's assume your data is on Sheet1.

In Sheet2 you have these row/column headers:

Book1
	A	B	C	D	E
1		T1	T2	T3	Ds
2	Lorry
3	Car
4	Ferrari
Sheet2

Enter this formula in B2:

=SUMIFS(Sheet1!$I$2:$I$40,Sheet1!$L$2:$L$40,B$1,Sheet1!$B$2:$B$40,$A2)

Copy across to E2 then down to B4:E4.

 

[Stu Dapples]

=SUMPRODUCT(--(B2:B40="Lorry"),--(I2:I40=""T1"),L2:L40)

lenze

Hint:You can use a cell reference for your criteria to reduce the number of formulas needed

=SUMPRODUCT(--(B2:B40=AB1),--(I2:I40=AB2),L2:L40)

I think you cracked my friend!! I need a little tweaking but it looks good! Many thanks