Sumifs with reference to multiple columns having same name

[rakesh1510]

what to keyin sumrange, where I have sums in 2-3 columns having same column name?
Criteria1:- suppose column names are Mag, Sha, Kic, Din, Mag, Sha.
Criteria 2:- 1,2,3...n.( S.no)

How to key in sumrange if one of my criteria is column names(lying in two-three columns) and other criteria is s.no.?

is there any other method to sum the requisite amount.?
Look into file attached and help me.

 

[AliGW]

Try this:

=SUMPRODUCT(($D$2:$J$2=D12)*($C$3:$C$7=C13),$D$3:$J$7)

 

[rakesh1510]

Thanks AliGW,
Thats working.

 

[Juniad]

You could use this method also..


Select the area as selected in Screenshot and go to Formula tab and create the name range as highlighted in no 1 and select check box Left Column as shown in screenshot and after that u can use simple sumif function.
=SUMIF($D$2:$J$2,$D$12,INDIRECT($C$13))

 

[bosco_yip]

Another Sumif function solution without using name range and Indirect function

=SUMIF(D2:J2,D12,INDEX(D3:J7,MATCH(C13,C3:C7,0),0))

Regards
Bosco