sumif() ?

[photologue]

I have the following spreadsheet ...

	A	B	C	D	E	F	G	H
1	Date	Stage	Product	Scrap cost	Date	Product	Material Waste	Process Waste
2	02/07/2014	Printed	WhiteMug11oz	£1.20	02/07/2014	LuggageTags
3	02/07/2014	Printed	WhiteMug11oz	£1.20		FridgeMagnets
4	02/07/2014	Printed	WhiteMug11oz	£1.20		Keyring
5	02/07/2014	Material	WhiteMug11oz	£0.95		Coaster
6	02/07/2014	Printed	LuggageTags	£1.21		ToteBag
7	02/07/2014	Printed	LuggageTags	£1.21		MouseMat
8	02/07/2014	Material	Photopanel10x8	£4.95		Jigsaw
9	02/07/2014	Material	Photopanel10x8	£4.95		WhiteMug11oz
				#N/A		BlackMug11oz
				#N/A		WhiteMug15oz
				#N/A		Photopanel7x5
				#N/A		Photopanel10x8

<colgroup><col><col><col><col><col><col span="3"><col></colgroup><tbody>
</tbody>

AS this spreadsheet will be recording product scrap over time and will have many entries over time, I wanted to do a quick table to show a summary of material and process waste for any given date, my table E1 to H14 above.
Have been playing with sumif and product functions but cannot get a result, for instance in cell G2 :-
If the date in A:A equals the date in E2
and
if the stage in B:B equals "Material"
and
if the product in C:C equals F2
then
sum all the relevant scrap costs from D:D

Help is very much appreciated

 

[Caribeiro77]

I have the following spreadsheet ...

	A	B	C	D	E	F	G	H
1	Date	Stage	Product	Scrap cost	Date	Product	Material Waste	Process Waste
2	02/07/2014	Printed	WhiteMug11oz	£1.20	02/07/2014	LuggageTags
3	02/07/2014	Printed	WhiteMug11oz	£1.20		FridgeMagnets
4	02/07/2014	Printed	WhiteMug11oz	£1.20		Keyring
5	02/07/2014	Material	WhiteMug11oz	£0.95		Coaster
6	02/07/2014	Printed	LuggageTags	£1.21		ToteBag
7	02/07/2014	Printed	LuggageTags	£1.21		MouseMat
8	02/07/2014	Material	Photopanel10x8	£4.95		Jigsaw
9	02/07/2014	Material	Photopanel10x8	£4.95		WhiteMug11oz
				#N/A		BlackMug11oz
				#N/A		WhiteMug15oz
				#N/A		Photopanel7x5
				#N/A		Photopanel10x8

<tbody>
</tbody>

AS this spreadsheet will be recording product scrap over time and will have many entries over time, I wanted to do a quick table to show a summary of material and process waste for any given date, my table E1 to H14 above.
Have been playing with sumif and product functions but cannot get a result, for instance in cell G2 :-
If the date in A:A equals the date in E2
and
if the stage in B:B equals "Material"
and
if the product in C:C equals F2
then
sum all the relevant scrap costs from D:D

Help is very much appreciated

if the product in C:C equals F2

The criteria is always F2 or is all F column?

 

[photologue]

[/COLOR]The criteria is always F2 or is all F column?

I would be dynamic. Example above, the formula for cell G2 criteria would be F2, formula for cell G3, criteria would be F3 etc.

 

[Caribeiro77]

Maybe this in G2

=SUMPRODUCT(--($A$2:$A$9=$E$2);--($B$2:$B$9="Material");--($C$2:$C$9=$F2);$D$2:$D$9)

 

[photologue]

Maybe this in G2

=SUMPRODUCT(--($A$2:$A$9=$E$2);--($B$2:$B$9="Material");--($C$2:$C$9=$F2);$D$2:$D$9)

Almost ...
Turns out you and I were making same mistake ...
=SUMPRODUCT(--($A$2:$A$9=$E$2),--($B$2:$B$9="Material"),--($C$2:$C$9=$F2),$D$2:$D$9)
comma, not semicolon (arghh!)

Thanks for help

 

[Caribeiro77]

That was not a mistake.... i just forgot to change ; by , cause i'm using excel out of USA and my excel version(Portuguese) and all excel european version's uses the ; instead ,

 

[photologue]

I am in UK and our version uses comma, not like us to copy USA !

 

[Caribeiro77]

I am in UK and our version uses comma, not like us to copy USA !

Really thought it was just in US that they use the comma to separate function arguments.