Sumifs or another way round

[rdev]

I have been stuck with this problem of having a sumifs formula to extract data from a table and place it in anothet format , see attached , I want to sum the data in column F& G based on the country and code and year
Any help would be appreciated . The file is shared

sumif.xlsx - Google Drive

 

[Beyond_avarice]

Insert the following in Cells J13:M13 and autofill down.

=SUMIFS($F$4:$F$16,$E$4:$E$16,$I13,$D$4:$D$16,J$10)

=SUMIFS($G$4:$G$16,$E$4:$E$16,$I13,$D$4:$D$16,K$10)

=SUMIFS($F$4:$F$16,$E$4:$E$16,$I13,$D$4:$D$16,L$10)

=SUMIFS($G$4:$G$16,$E$4:$E$16,$I13,$D$4:$D$16,M$10)

<tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

3​

Cluster

BUCO

Conp Name

Acc_code

Y2016

Y2017

4​

Bldg

SM

Dubai

1101​

3400​

6800​

5​

Bldg

SM

Dubai

1203​

3200​

6400​

6​

Bldg

SM

Dubai

1401​

4900​

9800​

7​

Bldg

SM

Dubai

1203​

3100​

6200​

8​

Bldg

SM

Dubai

1611​

4100​

8200​

9​

Bldg

SM

Dubai

1609​

4800​

9600​

10​

Bldg

SM

Dubai

1401​

5000​

10000​

Dubai

Dubai

Sri Lanka

Sri Lanka

11​

FB

Sri Lanka

1203​

1500​

3000​

Y2016

Y2017

Y2016

Y2017

12​

FB

Sri Lanka

1611​

4000​

8000​

SM

SM

FB

FB

13​

FB

Sri Lanka

1401​

4200​

8400​

1101​

3400​

6800​

3100​

6200​

14​

FB

Sri Lanka

1203​

3900​

7800​

1203​

6300​

12600​

10400​

20800​

15​

FB

Sri Lanka

1101​

3100​

6200​

1401​

9900​

19800​

4200​

8400​

16​

FB

Sri Lanka

1203​

5000​

10000​

1611​

4100​

8200​

4000​

8000​

17​

1609​

4800​

9600​

0​

0​

18​

In J13 enter, copy across, and down:

=SUMIFS(INDEX($F$4:$G$16,0,MATCH(J$11,$F$3:$G$3,0)),$E$4:$E$16,$I13,$D$4:$D$16,J$10,$C$4:$C$16,J$12)