Multiple Criteria SUMIFS

[Dom.Knight]

Hello,

On sheet 3 In columns A:D I have a kilo summary of species/ presentation/ total boxes and total kilos from information entered on sheet 1.

In columns F:L i have another summary prelimunary to find the Highest/ lowest/ average kilo price based on specie per size. I have entered 2 new columns, (I) which add ups the total boxes for each specie per grade and (H) Which will show the presentaion of each specie per grade.

However im struggling to update the information. For example in columns A:D if there is the same specie with both types of presentation it will seperate them and show the boxes/ kilos seperatetly. If i try to use the same idea in Columns F:I it will only change to the first presenation entered on sheet 1.

Ive got an example in red for What im trying to achive. Im just looking for Formulae solutions thanks i cant get on with pivots.

[Fluff13]

Are you looking for an output of
Excel 2016 (Windows) 32 bit

	F	G	H
2	Species	Grade	Presenattion
3
4
5
6	HAD	4	WF
7	HAD	4	GH
8	ANF	4	GH
9	ANF	5	GH
10	ANF	6	GH
11	SQU	U	WF
12	RJM	4	GH

Sheet: sheet3

or
Excel 2016 (Windows) 32 bit

	W	X	Y
6	HAD	4	WF
7	HAD	4	GH
8	ANF	4	GH
9	SQU	U	WF
10	RJM	4	GH
11	PLE	2	GH
12	LEM	2	GH
13	POL	3	GH
14	HKE	4	GH

Sheet: sheet3

[Dom.Knight]

Looking at the first output. F:H

[Fluff13]

Ok, in F2
=IFERROR(INDEX(sheet1!$B:$B,AGGREGATE(15,6,ROW(sheet1!$B$7:$B$1006)/ISNUMBER(sheet1!$I$7:$I$1006)/ISNA(MATCH(sheet1!$B$7:$B$1006&"@"&sheet1!$D$7:$D$1006&"@"&sheet1!$C$7:$C$1006,$F$2:$F5&"@"&$G$2:$G5&"@"&$H$2:$H5,0)),1)),"")

just change the index column for cols G & H
Then in I2
=IF($F6<>"",ROUNDUP(SUMIFS(sheet1!$E$7:$E$150,sheet1!$B$7:$B$150,F6,sheet1!$D$7:$D$150,G6,sheet1!$C$7:$C$150,H6),0),"")

[Dom.Knight]

That works great thanks.

Only one snag i hadnt thought about is my kilo prices J:L dont differentiate by presentation. If i have specie "haddock" grade "4" but with each presentaion "gh" and "wf" id like them to give seperate prices. has it stands now if im only getting prices per grade.

[Fluff13]

For J use
=AGGREGATE(15,6,sheet1!$I$7:$I$1006/(sheet1!$B$7:$B$1006=$F6)/(sheet1!$D$7:$D$1006=$G6)/(sheet1!$C$7:$C$1006=$H6),1)
And for L use
=AVERAGE(J6:K6)

[Dom.Knight]

Thanks for your help

For L thoe the Average should find the Average kilo price from Sheet1 for each specie per grade. Your function only finds the average from J and K

Also What would be your formulae be for K ?

[Fluff13]

For col K just use the col J formula, but change the 15 to 14, the same as you had originally.
The formula in col L returns the same same price as the formula you had originally (modified to account for the extra column.

[Dom.Knight]

Many thanks i did try that this morning and it didnt work but perhaps mistyped as it works correctly now. Thanks

[Fluff13]

You're welcome & thanks for the feedback.

[Dom.Knight]

Would you be able to help me out with avergae price scenario column L

[Fluff13]

How about
=AVERAGEIFS(sheet1!$I$7:$I$1006,sheet1!$B$7:$B$1006,$F6,sheet1!$D$7:$D$1006,$G6,sheet1!$C$7:$C$1006,$H6)

[Dom.Knight]

Excellant. Many thanks for help with this matter. All work as they should.

[Fluff13]

You're welcome & thanks for the feedback.