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
[TABLE="width: 443"]
<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>[TR]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 138, bgcolor: transparent"]column E[/TD]
[TD="class: xl65, width: 194, bgcolor: transparent"]column F[/TD]
[TD="class: xl65, width: 112, bgcolor: transparent"]column G[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]column H[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]CROPS[/TD]
[TD="class: xl68, bgcolor: transparent"]Area (ha)[/TD]
[TD="class: xl69, bgcolor: transparent"]% of arable land[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row27[/TD]
[TD="class: xl70, bgcolor: transparent"]SB[/TD]
[TD="class: xl75, bgcolor: transparent"]10.00[/TD]
[TD="class: xl73, bgcolor: transparent"]14%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row28[/TD]
[TD="class: xl70, bgcolor: transparent"]SO[/TD]
[TD="class: xl75, bgcolor: transparent"]30.00[/TD]
[TD="class: xl73, bgcolor: transparent"]42%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row29[/TD]
[TD="class: xl70, bgcolor: transparent"]WB[/TD]
[TD="class: xl75, bgcolor: transparent"]4.00[/TD]
[TD="class: xl73, bgcolor: transparent"]6%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row30[/TD]
[TD="class: xl70, bgcolor: transparent"]WW[/TD]
[TD="class: xl75, bgcolor: transparent"]0.00[/TD]
[TD="class: xl73, bgcolor: transparent"]0%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row31[/TD]
[TD="class: xl70, bgcolor: transparent"]WOSR[/TD]
[TD="class: xl75, bgcolor: transparent"]20.00[/TD]
[TD="class: xl73, bgcolor: transparent"]28%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row32[/TD]
[TD="class: xl70, bgcolor: transparent"]TSWS[/TD]
[TD="class: xl75, bgcolor: transparent"]0.00[/TD]
[TD="class: xl73, bgcolor: transparent"]0%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row33[/TD]
[TD="class: xl70, bgcolor: transparent"]Legumes[/TD]
[TD="class: xl75, bgcolor: transparent"]5.00[/TD]
[TD="class: xl73, bgcolor: transparent"]7%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row34[/TD]
[TD="class: xl70, bgcolor: transparent"]HF[/TD]
[TD="class: xl75, bgcolor: transparent"]0.00[/TD]
[TD="class: xl73, bgcolor: transparent"]0%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row35[/TD]
[TD="class: xl70, bgcolor: transparent"]Potatoes[/TD]
[TD="class: xl75, bgcolor: transparent"]3.00[/TD]
[TD="class: xl73, bgcolor: transparent"]4%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row36[/TD]
[TD="class: xl70, bgcolor: transparent"]Other Arable[/TD]
[TD="class: xl75, bgcolor: transparent"]0.00[/TD]
[TD="class: xl73, bgcolor: transparent"]0%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row37[/TD]
[TD="class: xl70, bgcolor: transparent"]FALLOW[/TD]
[TD="class: xl75, bgcolor: transparent"]0.00[/TD]
[TD="class: xl73, bgcolor: transparent"]0%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row38[/TD]
[TD="class: xl71, bgcolor: transparent"]TGRS[/TD]
[TD="class: xl76, bgcolor: transparent"]0.00[/TD]
[TD="class: xl74, bgcolor: transparent"]0%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RoW39

[/TD]
[TD="bgcolor: transparent"]TABLE 2[/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row40[/TD]
[TD="class: xl72, bgcolor: transparent"]Main Crop[/TD]
[TD="class: xl79, bgcolor: transparent"]SO[/TD]
[TD="class: xl66, bgcolor: transparent"]30.00 ha[/TD]
[TD="class: xl80, bgcolor: transparent"]42%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row41[/TD]
[TD="class: xl72, bgcolor: transparent"]Two main crops[/TD]
[TD="class: xl79, bgcolor: transparent"]SO, WOSR[/TD]
[TD="class: xl66, bgcolor: transparent"]50.00 ha[/TD]
[TD="class: xl78, bgcolor: transparent"]70.00%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row42[/TD]
[TD="class: xl72, bgcolor: transparent"]Third and subsequent crops[/TD]
[TD="class: xl79, bgcolor: transparent"]SB,WB,LEGUMES,POTATOES[/TD]
[TD="class: xl66, bgcolor: transparent"]22.00 ha[/TD]
[TD="class: xl78, bgcolor: transparent"]30.00%[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]TABLE 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD][/TD]
[TD]CROPS[/TD]
[TD]Area (ha)[/TD]
[TD]% of arable land[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD][/TD]
[TD]SB[/TD]
[TD]

10​

[/TD]
[TD]

14%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD][/TD]
[TD]SO[/TD]
[TD]

30​

[/TD]
[TD]

42%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD][/TD]
[TD]WB[/TD]
[TD]

4​

[/TD]
[TD]

6%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD][/TD]
[TD]WW[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

31​

[/TD]
[TD][/TD]
[TD]WOSR[/TD]
[TD]

20​

[/TD]
[TD]

28%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

32​

[/TD]
[TD][/TD]
[TD]TSWS[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

33​

[/TD]
[TD][/TD]
[TD]Legumes[/TD]
[TD]

5​

[/TD]
[TD]

7%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

34​

[/TD]
[TD][/TD]
[TD]HF[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

35​

[/TD]
[TD][/TD]
[TD]Potatoes[/TD]
[TD]

3​

[/TD]
[TD]

4%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

36​

[/TD]
[TD][/TD]
[TD]Other Arable[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

37​

[/TD]
[TD][/TD]
[TD]FALLOW[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

38​

[/TD]
[TD][/TD]
[TD]TGRS[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

39​

[/TD]
[TD]TABLE 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

40​

[/TD]
[TD][/TD]
[TD]Main Crop[/TD]
[TD]SO[/TD]
[TD]30.00 ha[/TD]
[TD]

42%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

41​

[/TD]
[TD][/TD]
[TD]Two main crops[/TD]
[TD]SO, WOSR[/TD]
[TD]50[/TD]
[TD]70%[/TD]
[TD][/TD]
[TD]CRITERIA[/TD]
[TD]CRITERIA[/TD]
[/TR]
[TR]
[TD]

42​

[/TD]
[TD][/TD]
[TD]Third and subsequent crops[/TD]
[TD]SB,WB,LEGUMES,POTATOES[/TD]
[TD]22[/TD]
[TD]31%[/TD]
[TD][/TD]
[TD]SO[/TD]
[TD]SB[/TD]
[/TR]
[TR]
[TD]

43​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WOSR[/TD]
[TD]WB[/TD]
[/TR]
[TR]
[TD]

44​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Legumes[/TD]
[/TR]
[TR]
[TD]

45​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Potatoes[/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]TABLE 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD][/TD]
[TD]CROPS
[/TD]
[TD]Area (ha)
[/TD]
[TD]% of arable land
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD][/TD]
[TD]SB
[/TD]
[TD]

10​

[/TD]
[TD]

14%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD][/TD]
[TD]SO
[/TD]
[TD]

30​

[/TD]
[TD]

42%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD][/TD]
[TD]WB
[/TD]
[TD]

4​

[/TD]
[TD]

6%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD][/TD]
[TD]WW
[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

31​

[/TD]
[TD][/TD]
[TD]WOSR
[/TD]
[TD]

20​

[/TD]
[TD]

28%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

32​

[/TD]
[TD][/TD]
[TD]TSWS
[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

33​

[/TD]
[TD][/TD]
[TD]Legumes
[/TD]
[TD]

5​

[/TD]
[TD]

7%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

34​

[/TD]
[TD][/TD]
[TD]HF
[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

35​

[/TD]
[TD][/TD]
[TD]Potatoes
[/TD]
[TD]

3​

[/TD]
[TD]

4%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

36​

[/TD]
[TD][/TD]
[TD]Other Arable
[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

37​

[/TD]
[TD][/TD]
[TD]FALLOW
[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

38​

[/TD]
[TD][/TD]
[TD]TGRS
[/TD]
[TD]

0​

[/TD]
[TD]

0%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

39​

[/TD]
[TD]TABLE 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

40​

[/TD]
[TD][/TD]
[TD]Main Crop
[/TD]
[TD]SO
[/TD]
[TD]30.00 ha
[/TD]
[TD]

42%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

41​

[/TD]
[TD][/TD]
[TD]Two main crops
[/TD]
[TD]SO, WOSR
[/TD]
[TD]50
[/TD]
[TD]70%
[/TD]
[TD][/TD]
[TD]CRITERIA
[/TD]
[TD]CRITERIA
[/TD]
[/TR]
[TR]
[TD]

42​

[/TD]
[TD][/TD]
[TD]Third and subsequent crops
[/TD]
[TD]SB,WB,LEGUMES,POTATOES
[/TD]
[TD]22
[/TD]
[TD]31%
[/TD]
[TD][/TD]
[TD]SO
[/TD]
[TD]SB
[/TD]
[/TR]
[TR]
[TD]

43​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WOSR
[/TD]
[TD]WB
[/TD]
[/TR]
[TR]
[TD]

44​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Legumes
[/TD]
[/TR]
[TR]
[TD]

45​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Potatoes
[/TD]
[/TR]
</tbody>[/TABLE]

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