sumif with range over multiple row and column

[Krevis]

	A	B	C	D	E	F
1		2013	2014	2015	2016
2	James		Peter			100	formula in question
3	Peter		James			108	formula in question
4	Mary			Peter	James	100	formula in question

<tbody>
</tbody>

I can't even think of proper question title. Hope i got it right.

How do I have something like sumif($B$2:$E$4,A2,F:F) where E4 is based on number of year starting from 2013?

So... for criteria of 4 years
James would have 108+100
Peter would have 100+100
but Mary would have 0.

 

[Chrisdontm]

One rule with SUMIF is that the sum range has to be the same size as the criteria range.
So you can't have it look up the whole column when you are just telling it to consider a small range.

So try this in cell: G2 and then copy it on down.....;

=SUMIF($B$2:$B$4,$A2,$F1:$F3)+SUMIF($C$2:$C$4,$A2,$F1:$F3)+SUMIF($D$2:$D$4,$A2,$F1:$F3)+SUMIF($E$2:$E$4,$A2,$F1:$F3)

There may be a shorter way to make this work, but at least this will work...

 

[Aladin Akyurek]

In F2 control+shift+enter and copy down:

=SUM(IF(MMULT(($B$2:$E$4=$A2)+0,TRANSPOSE(COLUMN($B$2:$E$4)^0)),$F$2:$F$4))

This approach becomes interesting in case you have more year columns to consider.