Returning results from table

[Rural]

Hello

Within a spreadsheet I have two tables. Table 1 contains the raw data and Table 2 needs to summarise the results.

For the first row in Table 2 I have used the following formula to obtain the required results.
=INDEX(E27:E38,MATCH(MAX(F27:F38),F27:F38,0))

In the next two rows 41 and 42 of Table 2 I don't know what formula to input though to get the desired result if it is even at all possible.
Colum F of Rows 41 and 42 needs to list the data whilst columns G & H needs to add up the results of the required data

Any ideas much appreciated.


TABLE 1

	column E	column F	column G	column H
	CROPS	Area (ha)	% of arable land
Row27	SB	10.00	14%
Row28	SO	30.00	42%
Row29	WB	4.00	6%
Row30	WW	0.00	0%
Row31	WOSR	20.00	28%
Row32	TSWS	0.00	0%
Row33	Legumes	5.00	7%
Row34	HF	0.00	0%
Row35	Potatoes	3.00	4%
Row36	Other Arable	0.00	0%
Row37	FALLOW	0.00	0%
Row38	TGRS	0.00	0%
RoW39	TABLE 2
Row40	Main Crop	SO	30.00 ha	42%
Row41	Two main crops	SO, WOSR	50.00 ha	70.00%
Row42	Third and subsequent crops	SB,WB,LEGUMES,POTATOES	22.00 ha	30.00%

<colgroup><col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <col width="194" style="width: 146pt; mso-width-source: userset; mso-width-alt: 7094;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

 

[MARZIOTULLIO]

	E​	F​	G​	H​	I​	J​	K​	L​
25​	TABLE 1
26​		CROPS	Area (ha)	% of arable land
27​		SB	10​	14%​
28​		SO	30​	42%​
29​		WB	4​	6%​
30​		WW	0​	0%​
31​		WOSR	20​	28%​
32​		TSWS	0​	0%​
33​		Legumes	5​	7%​
34​		HF	0​	0%​
35​		Potatoes	3​	4%​
36​		Other Arable	0​	0%​
37​		FALLOW	0​	0%​
38​		TGRS	0​	0%​
39​	TABLE 2
40​		Main Crop	SO	30.00 ha	42%​
41​		Two main crops	SO, WOSR	50	70%		CRITERIA	CRITERIA
42​		Third and subsequent crops	SB,WB,LEGUMES,POTATOES	22	31%		SO	SB
43​							WOSR	WB
44​								Legumes
45​								Potatoes

<tbody>
</tbody>

H41=SUMPRODUCT(SUMIF($F$27:$F$38,$K$42:$K$43,G$27:G$38)) copy across

h42=SUMPRODUCT(SUMIF($F$27:$F$38,$L$42:$L$45,G$27:G$38)) copy across

I41-I42 Format percent style

 

[Rural]

	E​	F​	G​	H​	I​	J​	K​	L​
25​	TABLE 1
26​		CROPS	Area (ha)	% of arable land
27​		SB	10​	14%​
28​		SO	30​	42%​
29​		WB	4​	6%​
30​		WW	0​	0%​
31​		WOSR	20​	28%​
32​		TSWS	0​	0%​
33​		Legumes	5​	7%​
34​		HF	0​	0%​
35​		Potatoes	3​	4%​
36​		Other Arable	0​	0%​
37​		FALLOW	0​	0%​
38​		TGRS	0​	0%​
39​	TABLE 2
40​		Main Crop	SO	30.00 ha	42%​
41​		Two main crops	SO, WOSR	50	70%		CRITERIA	CRITERIA
42​		Third and subsequent crops	SB,WB,LEGUMES,POTATOES	22	31%		SO	SB
43​							WOSR	WB
44​								Legumes
45​								Potatoes

<tbody>
</tbody>

H41=SUMPRODUCT(SUMIF($F$27:$F$38,$K$42:$K$43,G$27:G$38)) copy across

h42=SUMPRODUCT(SUMIF($F$27:$F$38,$L$42:$L$45,G$27:G$38)) copy across

I41-I42 Format percent style

Hello. Thanks for the reply. My initial query was possibly misleading. The data in column G and H of table 1 is stemming from another spreadsheet and a such can change. The purpose of Table 2 is therefore to react to any changes and summarise. As such a formula is required for columns F,G &H in rows 41 & 42.

Row 41 column H needs to be the sum of the two largest figures in column G of table 1. Row 41 column G needs to return the adjacent cell categories (i.e. the crop types of the largest areas, column F table 1) and similarly Row 41 column I needs to return the summed adjacent cell figures h of table 1

Row 42 is similar in that it needs to return the accumulated results of remaining crops where a positive area exists.

I suspect that this is possibly challenging!!!!!

Thanks