Help to create a new SUM IF Formula

[Dom.Knight]

Hi Im looking to add a new search parameter to my spreadsheet.

In columns A:G i enter data based on specie/ size/ presentation/ weight/ box/ price.

In columns M:P based on the specie i have index and sumif functions to pick out the amount of boxes/ kilos of each species based on the data i have already entered

I now want to add a new perameter which seperates specie based on presentation.

Example

M7 = COD N7= GH O7=64 P7=2240
M8 = HAD N8=GH O8=29 P8= 1160
M9 =HAD N9=WF O9=24 P9=960

[gue2013]

SUMIFS instead of SUMIF

[FDibbins]

Perhaps if you show some expected answers?

Also, is the formula in M pulling out unique entries, I see it is searching for criteria in B that dont exist (species and 0)?

[Dom.Knight]

Ive updated my attachment to show the results to which im looking for in columns R:U.

The formula in M is only picking out species which are in column B and ignores the two sub headings located in rows 47:50 and 93:96

[FDibbins]

Apologies, but still not sure which data you want extracted, and which is criteria?

Have you considered/tried using a PIVOT TABLE for this?

	M	N	O	P	Q	R	S	T
16	Sum of Total (Kg)	Column Labels
17	Row Labels	1	4	10	12	24	25	Grand Total
18	COD	35	140	350		840	875	2240
19	GH	35	140	350		840	875	2240
20	HAD	40	160			1920		2120
21	GH	40	160			960		1160
22	WF					960		960
23	WHG				1080			1080
24	GH				540			540
25	WF				540			540
26	Grand Total	75	300	350	1080	2760	875	5440

[gue2013]

I added a helper column and generates a unique list of combinations out of that.
Then I used SUMIFS for collecting the data (see attached file)

[Dom.Knight]

Hello and thanks for the replies but thats still not quite what im looking for.

In the data set which is columns A:G, I have enterned a merchant/ specie/ size/ gutted or whole/ number of boxes/ box weight/ box price.

For some species they will have both "GH/ WF" marked against them depending on the size.
For Exmaple row 14 and 15 both show specie "had" size "4" with "gh" and "wf" marked against them.

What im trying to achive is a formulae that will show the total kilos for gh had and wf had.(for every specie that may have this)
My current formulae in columns M:P adds up the total kilos and boxes for each specie that is entered however it does not seperate between gutted and whole fish (GH/ WF)

[CARACALLA]

	M	N	O	P
3	species	Presentation	boxes	Total (Kg)
4
5
6
7	COD	GH	64	2240
8	HAD	GH	29	1160
9	HAD	WF	24	960
10	WHG	GH	12	540
11	WHG	WF	12	540

M7=IFERROR(INDEX(B$7:B$46,SMALL(IF(FREQUENCY(IF(B$7:B$46<>"",MATCH($B$7:$B$46&$C$7:$C$46,$B$7:$B$46&$C$7:$C$46,0)),ROW(B$7:B$46)-ROW(B$7)+1),ROW(B$7:B$46)-ROW(B$7)+1),ROWS($B$7:$B7))),"")

Control+shift+enter

copy across and down



O7=IF($M7<>"",SUMIFS($E$7:$E$46,$B$7:$B$46,$M7,$C$7:$C$46,$N7),"")

copy down


P7=IF($M7<>"",SUMIFS($J$7:$J$46,$B$7:$B$46,$M7,$C$7:$C$46,$N7),"")


copy down

[Bo_Ry]

Please try at
M7:N7
=IFNA(INDEX(B$7:B$102,MATCH(0,-ISNUMBER(MATCH($B$7:$B$102&$C$7:$C$102,$M$3:$M6&$N$3:$N6,)),)),"")

O7
=IF(M7="","",SUMIFS(E$7:E$102,$B$7:$B$102,$M7,$C$7:$C$102,$N7))

P7
=IF(N7="","",SUMIFS(J$7:J$102,$B$7:$B$102,$M7,$C$7:$C$102,$N7))

or fews click with PIVOT

[Dom.Knight]

CARACALLA :- Yes thats what im looking for however is there away to adapt the formulae in M7 to ignore the sub headings in my attachment which are located in rows 47:50 and 93:96.

And also if i change numbers in column E to decimal is there away to include a round up formulae aswell.

[Dom.Knight]

BO_RY:- Yes what im looking for aswell. can your formulae be adjusted as my post above.

[JeteMc]

Try making the following modification to the array entered formula in cell M7. After activation*, drag the fill handle over to cell N7 and then, while M7:N7 are still selected, double click the right mouse button to copy down.

Please Login or Register  to view this content.

*Array entered formulas need to be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER before being copied.
Try making the following modification to the formula that populates column O: =IF($M7<>"",ROUNDUP(SUMIFS($E$7:$E$150,$B$7:$B$150,$M7,$C$7:$C$150,$N7),0),"")
When I tested the formulas columns M:P displayed the same output as that which is in columns R:U with the exception of cell P7. Doing a bit of filtering it would appear that P7 displays the correct result.
Let us know if you have any questions.